A TransferSpreadsheet question

[Zygor]

I'm currently running a
Docmd.TransferSpreadsheet acExport,,"MyTableName", "MyPathAndFileName.xls",True.

It is saving the table as a spreadsheet into a particular workbook. I "thought" yesterday it was working as it was supposed to. Today however, it is not. Today it is creating a new tab with the tab name MYSheet2. I want it to replace the worksheet MySheet.

Is there a way to do this? (Please don't tell me to pull the data in from Excel. This is not a viable option for my circumstances)

 

[kjv1611]

Is there a DeleteSpreadsheet method you could use for Excel to remove the old spreadsheet, and then replace it with the new spreadsheet?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Zygor]

That would solve the naming issue but would bring up a new issue. That being that there are formulas on other tabs referring to the offending sheet. By removing it the formulas get #NUM(or was it #REF?) and don't fix themselves once the new sheet is created. Thanks though.

 

[kjv1611]

Hmm, didn't think about that one.. Well, what about using:

DoCmd.SetWarnings False
DoCmd.SetWarnings True

??
You could possibly put the first line there just before the code for putting in the new tab/sheet, and the 2nd line just after that code. Have you tried that?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[lewds]

Hi..

If you are just updating data in the spreadsheet, then why not just link the spreadsheet and treat it as another table and use an update query. This would leave your formulas and references intact.

 

[kjv1611]

That's true, so long as the table/query will always be there in Access (I think).. Hmm, would it produce errors on the spreadsheet if the table were for some reason not found?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[Zygor]

I appreciate all the feedback and thoughts.

The problem with linking is two fold. One - the annoying questions of do you want to update....each time you open the spreadsheet. Second (and more important) is that I need the data to be a point in time, not updatable.

 

[kjv1611]

Hmm, I would think it still possible - you can set your database query in excel to where it does not ask if you want to update each time, and I think you can fix it to where it only takes the data once, and no more - either by saving the data, or just deleting the link once it's there.. or how about this, instead of a database query, do this:
Data -> Import External Data -> Import Data
It works the same, but doesn't link to the database, so I assume would not update each time opened... I'm sure it can be coded in Excel, and I would think Access as well - using an instance of the Excel Application..

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV