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

[John_G]

I got that too. I think what is happening is that when the spreadsheet opens, it is visible, and that's OK. However, if you (or I) close it with the close button (the X), instead of letting the Access VBA do it, you get the error. That will confuse your users no end.

Try making the spreadsheet invisible with x1.Visible = False instead of True - that worked for me.

[Lou_Reed]

Yes, that seemed to work. I just wish that I knew what was causing this to happen.


Anyway one final question which may be answered already, but here goes.

I have a SQL macro (or program) that lists all of the tables in the db. This also list one table item that I guess is a system table, but if that is all that it does wrongly then I can live with it.

I want that to be automated so when a user wants to export the contents of a table to an Excel sheet all she/he has to do is select the table and then it is exported.

Well we have some SQL code and a function VBA SUB.

I just need to know two to put these two together in the simplest way - that way it is less likely to crash. .


I guess that I can make the SQL run by attaching it to a button, but then the selected tabled is now the argument instead of testbook2 in the line

xl.Workbooks.Open ("C:\Users\\roy.rogers\Desktop\testbook2.xlsx")

I guess that it could be done by integrating the SQL code into the VBA code, but I am not sure how to do it.

Is there an easier way? If not how do I accomplish this by integrating SQL code into the VBA code?

I know the steps, but I just do not know how to take that selection and put into the line above.

You have the db from the previous post.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

Sorry, I made a mistake in the last post. The line should be:

Set rs = db.OpenRecordset("Table1")

not

xl.Workbooks.Open ("C:\Users\\roy.rogers\Desktop\testbook2.xlsx")

Please excuse my error.

Respectfully,

Lou Reed

[John_G]

Here you are. Just a couple of easy changes were all that was needed.

1. I replaced the select table button with a combo box. The combo has as its Row Source the query List Tables, and it displays the table names from that query (I get that strange system table (?) too). The combo then has the table name as its value when you make a selection.
2. Only one small change was needed to the VBA to export the table: I just changed
   Set rs = db.OpenRecordset("table1")
   to
   Set rs = db.OpenRecordset(Me!TableList)The recordset contains data from whatever table was selected in the combo box.
3. I added some error handling code to the VBA, so that if a user does select that system (?) table, they will get an error message. For some reason, even though that table name appears in the table list, Access thinks it does not exist and generates an error.

Let us know how it goes.

[Lou_Reed]

Four quick questions. The first is we know what the rowsource is for the newly added combo box, but we do not know (at least I do not) what the control source is.

Secondly is there a way to do this without using recordsets?

Thirdly, is there way to get that system table out of the combo box selection?

Fourthly, in the executable line rs = db.OpenRecordset(Me!TableList), what is TableList? I know that the query's name is List Tables, but that is not the criteria you used - you used TableList.

Any help appreciated. thanks n advance. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I do not get this. I believe that I have the entered the VBA code correctly. It is crashing on the line where Me!TableList is first used.

Then it goes all the way down to ExportError section, I am very confused as to why.

There is no code that would direct the program there. None.
The zipped db and the pdf files are attached. I really do not understand what I going on here.

Also, what us the difference between End Sub and Exit?

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

My code as it stands now seems to be increasing number of these strange tables. See attached pdf file. I am not sure if it is the code I am using or juts a characteristic of the VBA
system. In other words all the debugging in the world will not eliminate it. , also, still have the problem I mentioned in post #36.

It seems that it is just there. I am guessing the more that I run the program, the more strange table entries that I get.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[Lou_Reed]

I tried downloading your version of ExportExcel.zip and I could not run it. It need MS Excel Library 16.0 and only have 14.0.

I do not know if there is a work around.

I am stuck on why my program ails at the line

set rs = db.OpenRecordset(Me!TableList)

I just cannot get it ot run past that line.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed


Respectfully,


Lou Reed

[Lou_Reed]

Okay, I got the answer why the ImportExcel.accdb program would not run. I put in double quotes on (Me!TableList) and that was wrong. I still have the other questions however.

Any help appreciated. thanks in advance.

Respectfully,

Lou Reed

[John_G]

There is no code that would direct the program there. None.

False! The line in the code that does this is:

On Error GoTo ExportError

which says the if a runtime error occurs, go to the line ExportError: and continue execution from there. The term for this is "error handling", and EVERY function or sub in your code should have an error handling ablilty. That is normal good programming practice, and if done correctly, it can allow your code to keep running without crashing it.

I tried downloading your version of ExportExcel.zip and I could not run it. It need MS Excel Library 16.0 and only have 14.0.

That's because Access will automatically upgrade its references to later versions (e.g. going from 2010 to 2016), but when the DB is moved to a machine with an older version, it won't. You have to do it manually.

Open the references list as before. It should indicate that Excel 16.0 library is "MISSING". Scroll down the list of references until you find Microsoft Excel 14.0 Library. Click the checkbox to the left to select it, and click OK. Re-compile the database, and it should now work properly (barring any other errors, of course). It should not gove you the error about a missing library.

[Lou_Reed]

In the zipped file posted immediately above I am moving a table from MS Access to MS Excel. The code is behind the button in the exporttabletoecel form.I am unable to supply a zipped file right now since my winzip is not working and I do not know why.

Anyway, in the I am able to control on which line the Excel export process starts. I believe that chose rownumber = 2 so it would start on the second row of the Excel worksheet. That takes care of which line the Excel export starts on. However, it is much more complicated than that.

I do not want the first row of the table of data to be written to the Excel worksheet - no matter what line it is on. That line contains no data only columns names.

I want all lines to go from the Access table to Excel worksheet except the first line for reasons that I gave above.

The VBA code behind the command button shows me no easy way to do this.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

I am unable to supply a zipped file right now since my winzip is not working and I do not know why.

You shouldn't need WinZip as Windows 7 can do it. In Explorer, right-click on the file name, Select Send to >, and then click Compressed (zipped) folder. Windows will create a zipped file, and you can post that zipped file to the newsgroup as an attachment.

[Lou_Reed]

Ok, I have Windows 10. Will it work like this on Windows 10? I had to reset my PC last week. It went back and kept my files, but deleted some things like winzip.

I have an earlier version here.. These zipped files should work.

Respectfully,

Lou Reed

[Lou_Reed]

I put the zipped up files in the previous post so now all I want is to learn how to have the datable table in MS Access be exported to the Excel worksheet, but leaving out the table's first row. The row with the column titles.

That row is not required since the column titles are already on the Excel spreadsheet. It makes no sense to export column titles since they are already there.

Any help appreciated. Thanks in advance.

Respectfully,

Lou Reed

[John_G]

Your zipped database has no tables in it. Can you try it again, please?