Error on file importing from Access table to Excel

[Lou_Reed]

I am trying to export an Access Table to Excel. When I put Production Tracker - Rec Excel file in the browse box to export the table to and select export the table this is what happens. See the capture image.



It say subscript out of range and i do not know why.

Any help appreciated.

Respectfully,

Lou Reed

[John_G]

This is a time when the error handling doesn't help. Because you are getting an error, you want to be able to find out the code line that is causing it. To do that, you temporarily turn off all error handling. From any code module or the immediate pane (Ctrl-G), select Toold - Options from the menu. Click the General tab, and then under Error Trapping, select Break on All Errors.

Run your procedure again; this time you will get a runtime error which will allow you to use debug to see the line of code that caused the error.

In your case, it is this line: Set ws = x1.Worksheets("Expanded Tracker")

Think about what that code is trying to do, and why it is giving you the error.

[Lou_Reed]

I suspect that

Set ws = x1.Worksheets("Expanded Tracker")

is setting the ws value to the worksheet in Excel. That would mean to replace "Expanded Tracker" with "Sheet1".

Now please understand that it works on other Access tables it only fails on exporting the large table, test.table

A small one (table) is not a problem.

So is this the correct answer?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Okay, thought that that change in the VBA code would work. Replacing Expanded Tracker with Sheet1.

It did. Sort of.

But the VBA code failed on a succeeding line.

So I am not sure if it works or that I just created another problem.

Any the db and jpg shot of the new error are attached in zip files.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

It's failing on the line Set rs = db.OpenRecordset(Me!TableList) because you haven't set db yet.

insert the line set db = CurrentDB inmmediately before the OpenRecordSet and it should work.

[Lou_Reed]

Okay, thank so much for your help. I appreciate it. I was confused because it did seem to import some small Excel files.

My initial correction in Post #3 is correct then? I just did do the item to set db.

Thanks again.

respectfully,

Lou Reed

[John_G]

My initial correction in Post #3 is correct then?

Yes. When you Set ws = x1.Worksheets("worksheetname") , the worksheet must exist in the workbook, otherwise you will get an error.