use multiple ranges in excel file import

[kobiashi]

hi

so im building a method to import an excel spreadhseet into my database

so far i have got the method to select the file and import it, and im curious when i use the DoCmd.transferSpreadSheet can i select multiple ranges in a single sheet.

the issue i have is, the sheet im trying to import has header halfway down the form, also i only want some of the data, so can i do something like select the sheet and choose range

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Table_RunIns", selectFile(), Sheet(5)"A2:k34" & "A56:K150", True

 

[Ranman256]

no. that command only brings the whole sheet.

instead, attach the workbook as an external table,
then run an append query to import the columns and records you want.

 

[kobiashi]

thanks for the reply, but the workbook changes everyday as does the name of the file,

 

[CJ_London]

you can only specify one range per transferspreadsheet command.

if the position of the header and the size of the range changes, you can go into excel, select and name the range you want and save. Then use that range name in your transferspreadsheet.

On the other hand if there is some logic to the range you are selecting (such as where a column is a date and you want to import rows with a specific date) you can link to the spreadsheet - either a whole worksheet or a specified range of columns - then use an append query to select the rows you want. When linking if the top row does not contain headings, columns will default to F1, F2 etc. Or perhaps the heading row is always the same, so use a range e.g. A2:K1000

 

[The_Doc_Man]

The only other approach I could see for a spreadsheet that changes frequently is to learn how to apply Excel Application Objects to this process. You can search this forum for the topic and see a lot about Excel objects. Then you would be able to search the sheet for the parts you want and perform recordset-based operations. That's pretty much your choices: (A) one range per transfer command (B) map the spreadsheet as a table (C) open the spreadsheet as an application object.

The most versatile is (C), which unfortunately is also the most complex. The good news is that it has been done before and this forum should have multiple articles for you.