Using cells function while importing data from excel

[mahmud1180]

Is there any way to use cells function instead of a range like ( A1: AC100)? I need to use numbers as I want to iterate the process. I want to make different tables from a single spread sheet. Any suggestions?

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Futures" & i, "C:\users\Mahmud\Desktop\Project\Futures.xlsb" , True, "Sheet1!Cells(4,1),Cells(100,100)"

[ranman256]

I dont think that will work. You can only use SHEET1. (no range)
BUT, you can attach the xl sheet as a table, then build an import query that only imports what you need. Similar to cell range.

[mahmud1180]

Range works only if I put Sheet1!"A1:AC100"

The problem with making query after importing is that it will change the name of the fileds. It is difficult to iterate the name of every fields.

[ssanfu]

Options that I see:
1) You use VBA to loop through the Excel cells.
2) You could save the data in different worksheets and import each worksheet to a separate table.
3) Save the data as CSV files and import each CSV file into a separate table.
4) You could save the whole spreadsheet as a CSV file and use VBA to parse the data into different tables on-the-fly. (I do this)


You might check out Ken Snell's site
Ken's ACCESS Examples and Information for Working With EXCEL Workbook Files
http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

[mahmud1180]

I have tried to make separate sheet but now I am stuck with changing the sheet name dynamically. I tried also using a fixed name of the sheet and deleting the sheet before the next iteration begins. Still it does not work . Any idea? Thanks

[ssanfu]

The only thing I can suggest is using VBA to loop through the Excel cells.
A lot of code, but you can control everything that happens - which workbook is opened, what cells are read, which table(s) the data should go in...