DoCmd.TransferSpreadsheet and microsoft jet database problem,pls help

A

Asghaar

Registered User.
Local time
Tomorrow, 01:53
Joined
Jul 4, 2012
Messages
47
Hello all,


I have the a form with a button ( at least one of them does what i'll depict:) ) that should export some tables previously created by another command button ,to an existing excel file and each in a specifc cell range.
The file is copied form a shared location to the folder where the imported file is.
This path is kept in a simple string variable ( Public glbFilePath As String in a module )
When I run the vba to export my tables i get the error below :

The Microsoft Jet database engine could not find the object ".Make sure the object exists and that you spell its name and path name correctly
Click to expand...

I don't understand why,because i can't find something wrong with my code.
I evan try another database form scratch and it seems to get corrupted by the same error as well..
Any ideas how could I repair or find another way to trasfer my tabeles,but keep in mind that i need to have them in a specifc cell range.

Regards,

Code: 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_Operations_per_type", glbFilePath & "Raport.xls", True, "Details!A6:B12"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_Operations_per_status", glbFilePath & "Raport.xls", True, "Details!A16:B24"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_timeline_towards_the_Country", glbFilePath & "Raport.xls", True, "Details!E6:F12"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_timeline_towards_SSPO_from_req", glbFilePath & "Raport.xls", True, "Details!E16:F24"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_op_per_Service_counted", glbFilePath & "Raport.xls", True, "Details!A28:B60"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_op_per_country_counted", glbFilePath & "Raport.xls", True, "Details!E28:F60"
 
Try commenting out all of the exports other than one, and see if you can isolate it down to one that is causing trouble.

It looks like you are exporting entire tables, correct? The one call my application makes to that API, I export a DAO.QueryDef object, as follows:

Code: 
  'Export to an Excel file
  DoCmd.TransferSpreadsheet TransferType:=acExport, _
                            SpreadsheetType:=acSpreadsheetTypeExcel12Xml, _
                            TableName:=strQryName, _
                            FileName:=Me.CostedBOMFilename, _
                            HasFieldNames:=True
 
Hello,

I've already done that and found out that 3 work without any problems and 3 don't/
The ones commented below are the ones that don't work.
Yes i export all the data found in each table.
Don't understand exact what you mean with the api selection ?

Code: 
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_Operations_per_type", glbFilePath & "Raport.xls", True, "Details!A6:B12"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_Operations_per_status", glbFilePath & "Raport.xls", True, "Details!A16:B24"
   'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_timeline_towards_the_Country", glbFilePath & "Raport.xls", True, "Details!E6:F12"
   'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_timeline_towards_SSPO_from_req", glbFilePath & "Raport.xls", True, "Details!E16:F24"
   DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_op_per_Service_counted", glbFilePath & "Raport.xls", True, "Details!A28:B60"
   'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tbl_op_per_country_counted", glbFilePath & "Raport.xls", True, "Details!E28:F60"
 
Asghaar said:
Don't understand exact what you mean with the api selection ?
Click to expand...

I copy/pasted my one use of DoCmd.TransferSpreadsheet as reference to what I have success with, and further explained that I export based on a DAO.QueryDef object instead of exporting an entire raw table.Thus the reason for syntax differences.
 
I notice you specify a full square spot Access is suppose to plug in the data to the sheet.

Perhaps if you want to specify the bottom row, dynamically compute that having first counted the number of records in the table to be exported, then add that number to the starting row. I am thinking perhaps the error comes when there is not enough spreadsheet rows to export the table rows into.
 
Hello,

That's correct regarding the space where should be exported.
But don't think is because of the space - since i have more rows and columns in the range specified ,than the data that will be exported ( we are taking about tables very small ).
I'll try to see if using DAO for export will avoid this type of problem.
What is weird is that this code was working until a couple of days a go..


regards,
 
Hi,

I've checked the documentation before and did try naming the tables with a shorter name ( even like onlu 1, 2 etc)
For example i renamed the table tbl_timeline_towards_the_Country in tbl and worked,tried to do the same to the others..it stopped working..
Honestly don't know what to do.
If i did a macro works,but i must specify the exact address file,which in may case i don't - and using that variable was easier..

I'll try to take a better look again.
 
Hello,

After a good night of sleep and some late night reading.
I found something interesting regardinh data ranges and transferSpreadSheet - http://www.accessmvp.com/KDSnell/EXCEL_Export.htm#ExpRange ( fo anyone that wants to read ).
So i went in my excel and named the data ranges previously mentioned in the code and in my vba referred directly to that... AND now works without problems ( at least until now :) ).

For now i can conclude that was a named range problem.


Thank you very much mdlueck for the help and the good advices.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom