exporting data

[robert909]

Hi,

I'm using Access 2000 on Windows 2000 and got the following question:

In a database I'm using the following VBA to export the data from query "Q_report" to an Excel file:
DoCmd.TransferSpreadsheet acExport, , "Q_report", "C:\Data\export"

Now this works fine, I get the excel file with this VBA. But the problem is that all the data from text-fields start with a " ' " -sign. So in the Excel file I get for example : "'Product A" instead of "Product A".
Do you know how to avoid that?
If you run the query in Access, you don't get the "'"-sign.

Thanks for your help!

Robert
The Netherlands

 

[Bello]

Hi Robert,

I Honestly do not know how to accomplish that through VBA, but do found a way to help you.

1) Open a new Excel file
2) On the menu bar select 'Data' -> 'Import External Data' -> 'Import Data'
3) Follow the instruction on the screen to import your result Excel file (the one you exported from Access).

I've tested it and all the leading single quotes were automatically removed. Hope it works for you too.

Bello

 

[OPMCoordinator]

this works for me:

Sub lezzgo1()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Karls Morning Report", dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\OPMDATA\Karls Reports\Karls Morning report.xls")

xlApp.Windows(1).Visible = True
xlBook.Sheets("pdn").Range("a2").CopyFromRecordset rst
xlBook.Close savechanges:=True
xlApp.Quit

Set rst = Nothing
Set dbs = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

it opens the query *karls morning report* and sends it to an excell file named *karls morning report*, starting a the cell A2.

not 100% sure what all the code actually means as it was the result of a LOT of help from this forum, but as i said, it works.

nb - your references need to be up to scratch for this to work.

mike c

ps it this module is called from another module that is set to run on the on load event of the form selected as the startup form, but i guess you could hang it off a button.

 

[robert909]

Hi,

We're getting closer now! I've got one problem left.
So I'm trying to export this query. In the query are 2 variables that are entered in a form. So part of the SQL looks like:
" WHERE (((tblmain.wm_week)>=[Forms]![F_menu]![start_week_r1] And (tblmain.wm_week)<=[Forms]![F_menu]![end_week_r1])) "

Now your VBA gets stuck on the line:
Set rst = dbs.OpenRecordset("Q_qty_val_report", dbOpenDynaset)
The error message is : Run time error 3061 "Too few parameters, expected 2"

If I run the VBA on a query without a "Form-variable", I don't get this error message.

Do you know how to solve this?

Thanks a lot for your help!

Robert

this works for me:

Sub lezzgo1()

Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Karls Morning Report", dbOpenDynaset)
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Open("C:\OPMDATA\Karls Reports\Karls Morning report.xls")

xlApp.Windows(1).Visible = True
xlBook.Sheets("pdn").Range("a2").CopyFromRecordset rst
xlBook.Close savechanges:=True
xlApp.Quit

Set rst = Nothing
Set dbs = Nothing
Set xlBook = Nothing
Set xlApp = Nothing

End Sub

it opens the query *karls morning report* and sends it to an excell file named *karls morning report*, starting a the cell A2.

not 100% sure what all the code actually means as it was the result of a LOT of help from this forum, but as i said, it works.

nb - your references need to be up to scratch for this to work.

mike c

ps it this module is called from another module that is set to run on the on load event of the form selected as the startup form, but i guess you could hang it off a button.

 

[OPMCoordinator]

hello there!

perhaps i should have qualified my earlier assertions - the code works... for me!

i dont think i have come accross that error before, but what i would do first is to define the parameters in your query.

ie go into the design view of your query, in the main menu bar at the top is "query". in that sub menu is "parameters".

enter what the parameter is, in your case [forms]![f_menu]![start_week_r1] and tell it that it is a date (if that is what it is)

do that for all parameters and see if that works.

otherwise, try "opensnapshot" instead of "opendynaset" in the vba code and see if that works. i tried to find what the difference between the two is on my help screens, but they are all stuffed up and i cant find it.

let me know how you get on.

mike c

ps i am doing this from memory now, so if anyone knows better, feel free to correct or enlighten me!

 

[robert909]

Hi,

I defined the 3 parameters that I'm using, but I still get the error message "Too few parameters, expected 3". I'm using a UNION-query. Could that be the problem?

I also tried "opensnapshot" but that doesn't work either, same error -message...

Any other suggestions?
Appreciate your help!

Robert

 

[OPMCoordinator]

could you post all of your query SQL?

then ill have a look and a play and see what i can come up with.

mike c

 

[robert909]

Mike,

I got a hint from a colleague of mine which solved the problem.
I replaced:
DoCmd.TransferSpreadsheet acExport, , "Q_report", "C:\Data\export"
with:
DoCmd.OutputTo acOutputQuery, "Q_report", acFormatXLS, "C:\Data\export"

This works, the single quotes are gone!

But thanks for your help anyways!

Robert