Solved

export an access table into excel using vbscript

Posted on 2006-07-12
4
1,248 Views
Last Modified: 2008-01-09
Hi

I have a protected database which does not let me add modules, macros, queries or specifications to it.
I need to export the table from the database called "maintable" to excel.

Because i cant add a specification i can't use DoCmd.TransferText, so used DoCmd.TransferSpreadsheet

I know the following code works if run from a module ....

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "maintable", "C:\test.xls", True

but when i put it into a vbscript then it doesnt work.
I get error saying ...
"The microsoft jet database engine could not find the object 'C:\test.xls'. Make sure the object exists and that you spell its name and the path name correctly
error code 800A0BC3

I cant figure out why this is happening. Any ideas?
0
Comment
Question by:Sailo100
  • 3
4 Comments
 
LVL 11

Expert Comment

by:pootle_flump
ID: 17090234
Hi

What's your script?
0
 
LVL 11

Accepted Solution

by:
pootle_flump earned 250 total points
ID: 17090369
This code works fine:

SET oAcc = CREATEOBJECT("Access.Application")

oAcc.OpenCurrentDatabase "RemoteDB.mdb"

oAcc.DoCmd.TransferSpreadsheet 1, 8, "MyTable", "C:\test.xls", True ' acExport = 1, acSpreadsheetTypeExcel9 = 8
0
 

Author Comment

by:Sailo100
ID: 17090990
Hi

that works great thanks, one more question if was importing data into access then would
acImport = 2 ??
0
 
LVL 11

Expert Comment

by:pootle_flump
ID: 17092329
Easiest way to check - debug the constants in the immediate window of VB\ VBA

?acImport
 0
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question