Import Excel Data from Ranges on a Worksheet

[Guest]

Hi, please help...

I am trying to import data from a series of ranges on a worksheet and import
to tables in my database. This code below will import the whole worksheet
into a table but thats not any good, I need to import a range (I have named
them in excel)

How can I do this?

Private Sub cmdImport_Click()
strPath = "\\titan\home\jeremyl1\"
strFileName = Dir(strPath & "5HG_Feb_Comm.xls")
MsgBox "Data Loading...", vbInformation, "Jez"
If Len(strFileName) <> 0 Then
DoCmd.TransferSpreadsheet acImport, , strTempTable, strPath &
strFileName, True
End If
MsgBox "Import Complete", vbInformation, "Jez"
End Sub

 

[Guest]

Use the Range argument of the Transfer Spreadsheet. You can specify the
sheet name and the cell range like this:
"SheetName!A15:Q:75"

 

[Guest]

Dave, Thanks for the reply...

In the code below where should I add the range and I have the range named,
can that be but instead of "SheetName!A15:Q75"?

also if there are a number of ranges to import what is the best way of doing
this?

Private Sub cmdImport_Click()
strPath = "\\titan\home\jeremyl1\"
strFileName = Dir(strPath & "5HG_Feb_Comm.xls")
MsgBox "Data Loading...", vbInformation, "Jez"
If Len(strFileName) <> 0 Then
DoCmd.TransferSpreadsheet acImport, , strTempTable, strPath &
strFileName, True
End If
MsgBox "Import Complete", vbInformation, "Jez"
End Sub

Jez

 

[Guest]

Yes, if you have the range named in the Excel spreadsheet, you can do it with
the TransferSpreadsheet:

DoCmd.TransferSpreadsheet acImport, , strTempTable, strPath &
strFileName, True, strRangeName