Importing Data From Excel

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

I have some code which should import data from an Excel Worksheet, but
it's not quite working.

The user is prompted to enter a workbook, a worksheet name and then
(via some other code) the columns for two fields to import. So before
this part of code is processed, we have workBookName, workSheetName,
importColumn1, and importColumn2.

I want to import the data on the worksheet "workSheetName" in column
numbers "importColumn1" and "importColumn2" to a new table in Access.
The top row of the data contains my field names.

How can I use the TransferSpreadsheet method to do this? I try

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"NewTable", workBookName, TRUE, ---MyRange---

.... but whatever in use for "MyRange" seems to fail.

For example, let's say I want to import columns 4 and 8. I can use
Excel's range.address property to get that Range1, the region of
interest in column 4, is D1:D4000, and Range2, the region of interest
in column 8 is H1:H4000. I thus try
MyRange = workSheetName & "!" & Range1 & "," & workSheetName & "!" &
Range2.
This translates to MyRange = "Sheet2!D1:D4000,Sheet2!H1:H4000",
but I get the error "Cannot find object
"Sheet2$$D1:D4000,Sheet2$$H1:H4000" (Note the funny thing it's doing to
the exclamation point).
I've tried other versions of MyRange, but it's just not working. Maybe
it's because the range is not a single region?
 
Rob said:
I have some code which should import data from an Excel Worksheet, but
it's not quite working.

The user is prompted to enter a workbook, a worksheet name and then
(via some other code) the columns for two fields to import. So before
this part of code is processed, we have workBookName, workSheetName,
importColumn1, and importColumn2.

I want to import the data on the worksheet "workSheetName" in column
numbers "importColumn1" and "importColumn2" to a new table in Access.
The top row of the data contains my field names.

How can I use the TransferSpreadsheet method to do this? I try

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8,
"NewTable", workBookName, TRUE, ---MyRange---

... but whatever in use for "MyRange" seems to fail.

For example, let's say I want to import columns 4 and 8. I can use
Excel's range.address property to get that Range1, the region of
interest in column 4, is D1:D4000, and Range2, the region of interest
in column 8 is H1:H4000. I thus try
MyRange = workSheetName & "!" & Range1 & "," & workSheetName & "!" &
Range2.
This translates to MyRange = "Sheet2!D1:D4000,Sheet2!H1:H4000",
but I get the error "Cannot find object
"Sheet2$$D1:D4000,Sheet2$$H1:H4000" (Note the funny thing it's doing to
the exclamation point).
I've tried other versions of MyRange, but it's just not working. Maybe
it's because the range is not a single region?
Click to expand...

Yes...you cannot use TransferSpreadsheet to import a range
of *non-contiguous cells*....

One option is to import entire range into temp table,
then pull specific columns from temp table.

{I am not Excel expert...I am repeating here what I have
learned from previous posts from experts, mostly Jamie}

OR...You could construct a query in code that gets these
2 columns via "ordinality of the columns"

'sounds like you have:

strNewTable = "NewTable1"
strWorkBook = "C:\wb.xls"
strCol1 = "D"
strCol2 = "H"
strMyRange = "[Sheet2!D1:H4000]"

'you want only cols D and H within defined range
'(you will need to convert D & H
' to "range ordinal" F1 & F5 in code)
'F1 = D
'F2 = E
'F3 = F
'F4 = G
'F5 = H

'assuming 1st col will always be start of range?
'if so, no conversion needed here...
strQCol1 = "F1"

'know 2nd should be "F5"
'maybe following will work for you (untested)
strQCol2 = "F" & CStr(Asc(strCol2) - Asc(strCol1) + 1)

strFrom = "[Excel 8.0;HDR=No;Database=" _
& strWorkBook & ";]." & strMyRange

'combine into make table query

'(you could alias your new table columns to
'something other than "Col1" & "Col2,"
'perhaps also via code)

strSQL = "SELECT " & strQCol1 & " As Col1, " _
& strQCol2 & " As Col2 " _
& "INTO " & strNewTable _
& " FROM " & strFrom
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError


in Immediate Window, debug print *should* show
(but all on one line)

SELECT
F1 As Col1,
F5 As Col2
INTO NewTable1
FROM
[Excel 8.0;HDR=No;Database=wb.xls;].[Sheet2!D1:H4000]
 
Back
Top