Question Table names change when exporting to excel.

[lws]

When exporting my tables from access to excel my table names change if they have a space in the name. Example table name "New Record" turns into "New_Record".

What I am trying to do is export the table data to excel than update my access program than imort the table data back. This way I can take a vertion of my access program and update/modify it as time permits than reinsert all current data with min down time.

The code I am using is as follows:

Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "\excel_out"
Set db = CurrentDb()
For Each td In db.TableDefs
If Left(td.Name, 4) = "MSys" Then

Else

MsgBox td.Name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
td.Name, out_file, True, Replace(td.Name, "dbo_", "")

End If
Next


Thanks for any help

 

[CJ_London]

Not quite sure what you are asking 0 why not just take a copy of the the access file and work on that instead?

With regards spaces in table and field names it is not generally a good idea - better no spaces or use an underscore.

Your routine also does not seem to have the file type extension - I think Excel9 is .xls but you need to include it in your file name

And , Replace(td.Name, "dbo_", "") is not used in export

 

[lws]

This access program is ever evolving, d/t The US EPA regulations changing every time the wind blows in a different direction. Administrating this access program is not my only responsibility. It may take several days to a week to make the changes we need. In the meantime, there is other people adding and editing records. To keep track of what has been added/changed would be a nightmare.

So when a change needs to be done, I take a copy of the program, do my changes. (However long that takes) insert the new program into place, transfer the (up to date) data tables into the new program.

The Table names are what they are. To late now.

Any help on exporting the tables over to excel with out the names of the tables changing would be greatly appreciated......

 

[lws]

With changing the acSpreadsheetTypeExcel9 to acSpreadsheetTypeExcel12 and not adding a file extention for out_file = CurrentProject.Path & "\excel_out". The xcel file is saved as a binary file (xlsb). However still no help with the name changing problem.


Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "\excel_out"
Set db = CurrentDb()
For Each td In db.TableDefs
If Left(td.Name, 4) = "MSys" Then

Else

MsgBox td.Name
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, _
td.Name, out_file, True
End If
Next

End Sub

 

[CJ_London]

Are you using a front end/backend? Still don't understand why you can't take a copy of the db and then transfer tables from the old db to the updated ones - what am I missing?

 

[lws]

My data and program are together so no I do not have a FE/BE db.

The simple answer why I would like to do this is, is TIME. I have close to 30 tables, and that takes time to bring in the data into each one.

I want to have two control buttons on a form. One to export all tables to a single excel file. When the new program is in place the other command button to import all data into the tables from a single excel file.

And no I do not want to split this into a FE/BE db due to this program is going to be used at other sites for their data, different file name, but on the same file server.

 

[lws]

OK so no answer on this question exists. Let’s go about it by way of another avenue. How can I change the name of the table so that it will propagate the new name to all of its dependencies?

 

[CJ_London]

So leaving your db structure aside, you want all tables in a single spreadsheet. Unfortunately transferspreadsheet won't do it.

You will need to use something like copyfromrecordset which involves creating an instance of Excel and using copyfromrecordset to copy each table to a difference range or worksheet

 

[spikepl]

If the sole purpose of using Excel is to have a temporary holding space for data, why not just copy the tables into a new db and copy back or append from there? No table name changes there.

For global changes of object names you could use v-tools (google it). For a working db, making such changes is risky, since one wrong global change can disable a working application, and recuperation can be complex.

 

[CJ_London]

When exporting my tables from access to excel my table names change if they have a space in the name. Example table name "New Record" turns into "New_Record".

If you are trying to copy to a range in excel, spaces are not allowed in rangenames so my guess is excel is changing it to an underscore.

Copying to different worksheets should solve the problem since you can have spaces in worksheet names

 

[CJ_London]

@CJ_London - TransferSpreadsheet can export to different sheets in a workbook. You don't need automation code to do it.

I have my own solutions on the basis that I thought i couldn't be done, but would be interested to know how it can be

Thanks

 

[CJ_London]

I'll have to give it a try - I always thought Transferspreadsheet always created a new workbook rather than appending to an existing one (if it existed)

Thanks for the example

 

[lws]

@Pat – The caption property is blank.

@spikepl – That’s pretty much what I have been doing. However it’s slow, but thanks for the suggestion.


The reason I have to do it the wrong “clunky” way is because of our file server system. You place your file/db on the file server. You than locate it and check it out. You make your edits or additions to the db than you check it back in. You cannot access another db residing on the file server from one you are manipulating, as in a FE/BE configuration. Also there are multiple sites using the copies of the program under different names. Site_1_copy, Site_2_copy, Site_3_copy so on and so forth. Each site being completely separate from the others, this is the way my powers to be want it.

I have decided to do a complete rewrite of the program. To make this work plus get rid of some abandoned features left over from the previous administrator.

Thanks for your help, but I feel that I had to do more defending of my methodology than explaining of the actual problem.