If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Importing Data From Excel
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 D14000, 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!D14000,Sheet2!H1:H4000", but I get the error "Cannot find object "Sheet2$$D14000,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? |
#2
|
|||
|
|||
Importing Data From Excel
"Rob" wrote: 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 D14000, 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!D14000,Sheet2!H1:H4000", but I get the error "Cannot find object "Sheet2$$D14000,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? 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] |
Thread Tools | |
Display Modes | |
|
|