Generalizing my VBA transfer of an MS Access table to MS Excel

[Lou_Reed]

In the attached two zipped files is my working db and the other is the Excel file that I am trying to send Access data the table to.

It generates an error that says it cannot find that Excel *.xlsm file. I cannot understand since the file is in the correct directory
with the correct path and correct name. The file extension that I used is *.xlsm. It is a macro enabled file. But it still says that it cannot find the
file.

What is going on. It seems that everything is fine.

Any help appreciated. Thansk in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I do not what was casing it to crash on the VBA line that connected with the Excel file, but it did.

It just does not seem to be e crashing there now; it is crashing on the line below the line where it
formerly crashed. The attached pdf file shows a Snagit screen shot.

Now I have also included the MS Access files that contains the VBA code. I am just not sure what is causing the crash.

What exactly causes this crash?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

When you post the screenshots, they only indicate WHERE the error occurred, not what the error is.
Please tell us what the error message says (doesn't have to be a screenshot), because without it we can only guess.

[John_G]

From post #16 -

Your VBA code has this : x1.Workbooks.Open ("C:\Users\james.yunker\Desktop\TestBook.xlsx"), with the file name in red,

but the zipped file you posted is called testing.xlsm.

Obviously one of them isn't right, and I suspect it is your VBA. Among other things, is your user name James.Yunker?

[Lou_Reed]

That was the first thing that I looked at and when I changed the filename in the VBA to match the filename in the directory it did not seem to matter.

It is now failing on the line after x1.Workbooks.Open ("C:\Users\james.yunker\Desktop\TestBook.xlsx"), with the file name in red,

this one. I thought the snagit shot did contain error description.

That is also my username of an employee that long ago left my organization.

Respectfully,

Lou Reed

[Lou_Reed]

Here it is again. The program showed a runtime error when it hit the line shown in the snagit picture.

It has happened before.

I just do not what is causing it and I am asking for assistance.

If it is obvious, well it is not to me.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

It's not obvious to me, either. I downloaded your database and the Excel file, and it ran without an error (after I changed the file location).
The only thing I can think of is that you have a wrong or missing reference. Can you post a screencap of the references you have set in your Database?

While in any code module, Select Tools - References, and you will see a dialog with the list of references.

I can't tell what your references are, because my Access 2016 automatically resets the list.

[Lou_Reed]

I was thinking that too. I was just unsure as to how to check it.

Respectfully,

Lou Reed

[Lou_Reed]

I think that I found it. The line in my code:

Set ws = xl.ActiveWorkSheet.Sheets.Add

should be

et ws = xl.ActiveWorkbook.Sheets.Add

This correction also compiles and is i believe the correct way to put it in.

I just need now to hook up the selection of the table to this code.

I know that it is this line that must be chnaged:


Set rs = db.OpenRecordset("Table1")

i am just sure what to put in instead of Table1.

Ny help apreciated. Thanks in advance.


Respectfully,

Lou Reed

[Lou_Reed]

In the zipped file which contains my current db, there is a difference in the action of the two subs that import MS Access Table1 They are shown in the screen capture1 and capture2.

The first column in the more generalized code on puts a number in the first row oftestbook.xlsx, but nowhere else and it i wrong anyway.

If the second sub is a true generalization then it should copy the Access Table1 exactly as it does in the specific cr less generalized code.

Where is the inconsistency in the code? Any help appreciated. thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I made a mistake. the error was the extra column and the presence of a 1 in it. It only shows up in the generalized code n the first row. Nowhere else.

How do i get rid of it.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

The code is working exactly as it should; the Excel spreadsheet matches the data in Table1. If you look at Table1, the first field is Called ID, and only record 1 has a value in it (The "1"). If you want that field not to be transferred to the spreadsheet, the easiest solution is to delete that field from the Table1 structure. You could also delete the column from the Excel spreadsheet after the export - no difference.

[Lou_Reed]

Okay, but why does it not also transfer this when I do the non-general Excel transfer? It seems to leave it off.

I have included a modified version of the Access program, plus the output on both Subs on Excel sheets. The Excel sheets should be the same. They are close, but they are not the same.

ignore the fact that they start on different rows, that should be unimportant.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

Youe subs Sub btnExportExcel_Click and ExportTabletoExcel_Click are different.

The first is not generalized for any table - it transfers only 4 fields, and refers to them by name. It also does not transfer the field names as column headers. The table field ID is not used.

The second is generalized for any table. It determines how many fields there are, and names of the fields from the recordset. It then transfers the names of the fields as column headers (if they are in row 1), followed by the data. It works for any table because it does not refer to recordset fields by name, only by field index number.

Different subs, different results.

[Lou_Reed]

I am going to use the generalized VBA code sub. I removed the specific VBA subs and only worried about the generalized code. Now I have got an error:

RunTime Error 91
Object variable or with block variable not set.

See pdf attachments. It says that a variable must be set, but I thought everything was alight.

I have included two pdf and a zipped files of the db. What is casuing this?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed