TransferSpreadsheet export to excel problem

[Damon Heron]

I have the following routine that I use to export table data to Excel. The
user selects a name from a combobox (cboTables) and then clicks a cmd
button:

On Error GoTo Err_CmdExport_Click
Dim Path As String
Dim TN As String
Path = Me.txtDirectory
TN = "tbl" & cboTables
DoCmd.TransferSpreadsheet acExport, SpreadsheetType:=8, TableName:=TN,
filename:=Path, Hasfieldnames:=True
exit_CmdExport_Click:
Exit Sub
Err_CmdExport_Click:
If Err <> 2501 Then
MsgBox Err.Description
End If
Resume exit_CmdExport_Click

This works fine for all the tables on the list except for one called
"tblTransactions". Then I get the msg "The Microsoft Jet database engine
could not find the object ". Make sure...."
When I rename tblTransactions to tblTrans it works fine. Then I rename it
again back to tblTransactions and it fails. All of the other tables have
shorter names. Is this some undocumented problem with long names in the
TransferSpreadsheet method? I don't want to have to rename my transactions
table as it is used thru out the db and it would be a pain.

Any ideas are appreciated.

 

[Ken Snell \(MVP\)]

Long name that you show is not the problem. I would suspect that the
spellings of the table name and what is in the combo box are not exactly the
same. Check that and verify.

 

[Damon Heron]

Sorry. I have checked and re-checked the spelling of Transactions many
times. Again, it works if I use a shorter table name, but doesn't when I
use "tblTransactions".

--
Damon Heron

Long name that you show is not the problem. I would suspect that the
spellings of the table name and what is in the combo box are not exactly
the same. Check that and verify.

 

[Damon Heron]

Get this. I rename the table "tblTransactionsinWarehouse"- the export
works. I rename it "tblTransaction" - the export works. I rename it
"tblTransactions" - it gives me the error msg! I am truly baffled.
--
Damon Heron

Sorry. I have checked and re-checked the spelling of Transactions many
times. Again, it works if I use a shorter table name, but doesn't when I
use "tblTransactions".

 

[Damon Heron]

Me again. I used help and I see that there is a "Transactions" property:
In a Microsoft Jet workspace, you can also use the Transactions property
with dynaset- or table-type Recordset objects. Snapshot- and
forward-only-type Recordset objects always return False.

Do any of you Gurus know if this could be my problem?

 

[Ken Snell \(MVP\)]

What is the SQL statement of the combo box's Row Source? What is the value
of the Bound Column property of the combo box?

A string text of Transactions from a combo box should not cause the problem
that you are describing.

 

[Damon Heron]

Ken,
I am using a value list to populate the combobox using
With me.cboTables
..AddItem "tablenames"
etc.
End With
statement, As I have said, it works on all of the other tables in the list
(7 of them) except the Transaction table.

 

[Ken Snell \(MVP\)]

You mention Transaction as the name of the table here, earlier you said it
is Transactions. Just want to be sure that you are using the same name
throughout.

Post the full code of how you're setting the value list.

 

[Damon Heron]

Ken,
It is Transactions, I just made a typo in the post. Here is the code for
loading the value list:

Private Sub Form_Open(Cancel As Integer)
With Me.cboTables
.AddItem "Containers", 0
.AddItem "Orders", 1
.AddItem "Customers", 2
.AddItem "Transactions", 3
.AddItem "Suppliers", 4
.AddItem "Blends", 5
.AddItem "Receipts", 6
.AddItem "Transfers", 7
End With
End Sub
The item numbers were added when I was trying to fix the problem, they
aren't really needed....

The other code I posted earlier. In that code, I am concatenating the
combobox name (which is more user friendly) with the real table names by the
line TN= "tbl" & cboTables. Could that be a problem? It works with all the
other tables, though, so I don't think it would be.

 

[Ken Snell \(MVP\)]

Most puzzling. The code that you're using with .AddItem looks fine; your
TransferSpreadsheet procedure looks fine (including the concatenation of the
"tbl" string with the words from the combo box).

Try importing the entire database into a new,empty database. Let's see if
there might be some subtle corruption that is causing this problem.

Then, in the new database, try it again. If you still get the error, let's
put a breakpoint on the TransferSpreadsheet step; when the code stops, put
the cursor over the TN variable in the code; what string does it contain?

 

[Damon Heron]

Arghhhh! New db, same problem. When I use the breakpoint, it shows the
value of TN as "tblTransactions" but still kicks out the error msg. I
rename the table "tblTransaction" and no error! I don't want to do it, but
at this point I may just rename the table and find and replace all instances
of tblTransactions to tblTransaction. That's gonna be a pain, but the user
wants the Excel sheets for Quickbooks use.

 

[Ken Snell \(MVP\)]

Could you email a sample of your database? This may be a bug, or it may be
something that I'm not considering in my "mental image".

If yes, zip it and send it me -- unmunge my reply address by removing the
words "this is not real" from the munged email address.

 

[Damon Heron]

Thanks for all your help, Ken. I discovered that the problem was unique to
the table "tblTransactions". If I named another table with the same name,
it transferred that one okay. So I created a new table and moved all of the
data into the new table, then renamed it tblTransactions and it works fine.
So I still don't know why, but there must have been some corruption in the
original table that was causing the problem. One of those eternal
mysteries.
Thanks again for paying attention.

 

[Ken Snell \(MVP\)]

Glad you found the source of the problem.