[AccessD] Importing from Excel

It might be easiest to import the file to Access using DoCmd

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet
Dim MyRange As Excel.Range

Set xlApp = New Excel.Application
Set xlWB = xlApp.Workbooks.Add("C:\MyTemplate.xlt")
Set db = CurrentDb

Set xlWS = xlWB.Sheets("ToImport")
With xlWS
    .Select
    Set rs = db.OpenRecordset("tblFromFile")
    .Cells(1, 1).CopyFromRecordset rs
End With

Set xlWS = Nothing
Set rs = Nothing

Michael R. Mattys
MapPoint & Access Dev
www.mattysconsulting.com

----- Original Message ----- 
From: <ewaldt at gdls.com>
To: <accessd at databaseadvisors.com>
Sent: Thursday, July 26, 2007 3:34 PM
Subject: Re: [AccessD] Importing from Excel


>I wrote the code below, which works really well and I'm very happy with
> it. This is code for Excel, though. What I need to do is the following:
>
> 1. Have the user find the Excel file (I can do this).
> 2. Have the code below apply to the Excel file (Don't know how to do this
> from within Access).
> 3. Have Access run the import process on the new worksheet (Not sure how
> to have it do that specific sheet; can it work on a workbook already in
> memory without saving the Excel file first?).
> 4. Do more processing of the data (I have this set up).
>
> So I really need help with #2 and maybe #3 above. Of course #2 is using
> the Excel object model, and that has to be taken into consideration.
>
> Any help?  Thanks.
>
> Thomas F. Ewald
> Stryker Mass Properties
> General Dynamics Land Systems
>
> ----------------------------------------------------------
> Sub PrepForImport()
>
> ' Created 7/26/2007 by ewaldt
>
> Dim intLastRow As Integer
> Dim intCount As Integer
> Dim intNewRow As Integer
> Dim oCell As Object
>
>    Sheets.Add
>    Sheets("Sheet1").Name = "ToImport"
>    Sheets("ToImport").Select
>
> 'Copy only rows where column B = "A"
>    intNewRow = 1
>
>    For Each oCell In Sheets("GDLS-SHC").Range("B:B")
>        If oCell.Formula = "A" Then
>            oCell.EntireRow.Copy
>            ActiveSheet.Paste
> Destination:=Worksheets("ToImport").Range("A" & intNewRow)
>            intNewRow = intNewRow + 1
>        End If
>    Next oCell
>
>    For Each oCell In Sheets("GDLS-C").Range("B:B")
>        If oCell.Formula = "A" Then
>            oCell.EntireRow.Copy
>            ActiveSheet.Paste
> Destination:=Worksheets("ToImport").Range("A" & intNewRow)
>            intNewRow = intNewRow + 1
>        End If
>    Next oCell
>
> 'Check for non-numeric in Pounds and Grams
>    For Each oCell In Sheets("ToImport").Range("C1:D" & intNewRow - 1)
>        If Not IsNumeric(oCell.Formula) Then
>            oCell.Formula = 0
>        End If
>    Next oCell
>
> End Sub
>
>
>
> This is an e-mail from General Dynamics Land Systems. It is for the 
> intended recipient only and may contain confidential and privileged 
> information.  No one else may read, print, store, copy, forward or act in 
> reliance on it or its attachments.  If you are not the intended recipient, 
> please return this message to the sender and delete the message and any 
> attachments from your computer. Your cooperation is appreciated.
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com