Why won't this program compile?

[Lou_Reed]

In the two files attached is a zip file that contains the database with a form that exports MS Access tables to MS Excel.

The code is show in a Snagit photograph that is the second file attached. This code fails on the first line.

Dim x1 As Excel.App...



I am not sure why. It could be because I have not installed

Microsoft Excel 14.0 Object Library

?

I am just curious as to why it fails on the first line.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

Yes, you're using early binding, which requires the Excel reference be checked in Tools/References.

[Lou_Reed]

Okay that is what I will do. By the way, what version of Microsoft Excel 14 Object Library should I be using? I chose 14? I have MS Excel 2010 and MS Access 2010.

Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

You need to choose the one on the computer. The problem arises if/when you distribute the application and other users have different versions of Excel installed. Most of us use late binding to avoid that:

https://support.microsoft.com/en-us/...-in-automation

[Lou_Reed]

Okay, how do I modify to avoid late binding.

I went to Tools --> References and

the Microsoft Excel 14 Object Library was already checked.

See attached files.

So I am unsure how to avoid this and I clearly need to know about late binding.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

One of us is blind. The Excel library is not one of those checked in your picture.

[Lou_Reed]

I was the one who could not see. I am the one. However, when I did check the MS Excel 14 Object Library it did compile that line, but failed on a latter line see PDF file). I think the syntax is correct. What is wrong?

Any help appreciated. Thank in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I fixed some error in my VBA code. I had two end sub lines. I only have one now, but I still
cannot get the line

Set db = CurrentDb

to compile. I am guessing that I must add another library since I see no syntax error.

Any help appreciated. Thank in advance.

Respectfully,

Lou Reed

[pbaldy]

You've declared the variable incorrectly, should be:

Dim db As DAO.Database

[Lou_Reed]

Yes, I caught that error thanks very much for pointing it out.

I have uploaded two zip files. One is an Access application and the other is an Excel application.

I am trying to import the table, Table1 into the Excel spreadsheet TestBook.xlxs from Access.

it is failing with an index out range error. I am unsure as to why.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

This line refers to a sheet that doesn't exist:

Set ws = xl.ActiveWorkbook.Sheets("newdata")

The next error you hit should be obvious.

[pbaldy]

And once you fix that, it "works", but doesn't really; see if you recognize why.

[Lou_Reed]

Wait a second now. i named the Excel sheet "TestBook" and yes I gave it the correct path:


V:\Users\Newport_j\Desktop\TestBook.xlsx

It should work with that, shouldn't it.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[pbaldy]

The sheet, not the file. Did you notice the line of code I posted? The sheet is named "Sheet1".

[Lou_Reed]

Okay, I guess i never really thought that each sheet had a name. It does: "sheet1". That is a pretty mundane name.

Is that really the name of the sheet - sheet 1?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed