Excel formatting outputTo

[grahamsyuk]

Hi Guys, i'm trying to export multiple queries to on excel file with multiple worksheets and keep the formatting from each query. i.e. one query on each sheet. This works fine for one query by using the following :

DoCmd.OutputTo acTable, "Query1", "MicrosoftExcelBiff8(*.xls)", "C:\My Documents\Multiple.xls", False, "", 0

However, i cant get it to work for multiple queries to multiple worksheets in the same excel file. Any help is greatly appreciated.

regards, G

 

[Call_Me_Sam]

Hi Guys, i'm trying to export multiple queries to on excel file with multiple worksheets and keep the formatting from each query. i.e. one query on each sheet. This works fine for one query by using the following :

DoCmd.OutputTo acTable, "Query1", "MicrosoftExcelBiff8(*.xls)", "C:\My Documents\Multiple.xls", False, "", 0

However, i cant get it to work for multiple queries to multiple worksheets in the same excel file. Any help is greatly appreciated.

regards, G

G, i'll have to admit i prefer to use this

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Temp", "C:\Documents and Settings\[I][B]your name[/B][/I]\My Documents\Multiple.xls", True

You will have to create a table first, then just copy the code and amend to trigger other exports.

I was confused slightly when you put about keeping formatting from the queries?

 

[grahamsyuk]

Excel worksheet and formatting

Hi there, thanks for your reply. I have craeted a temp table as you suggested but again i cant get it to keep the percentage formatting in some of the columns that is presnt in the query. I used the following code:

DoCmd.RunSQL "SELECT * into tempTable FROM [Query1] ;"

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


Apologies if i appear stupid here, im new to VBA

Regards, G

 

[Call_Me_Sam]

Hi there, thanks for your reply. I have craeted a temp table as you suggested but again i cant get it to keep the percentage formatting in some of the columns that is presnt in the query. I used the following code:

DoCmd.RunSQL "SELECT * into tempTable FROM [Query1] ;"

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


Apologies if i appear stupid here, im new to VBA

Regards, G

As Sir Jimmy used to say Now then, Now then, Now then!! what we have here is a tricky situation..if you right click and export table, and tick the little box to Save formatting then the spreadsheet has the formatting as defined...but where do you set that in the VBA???!! any one got a clue?

G, i will keep looking though..

 

[grahamsyuk]

outputTO

Hi Again, thanks for your endeavours. What i did find was that the following code gives my desired effect but not completely:

DoCmd.OutputTo acOutputQuery, "Query", "MicrosoftExcel(*.xls)", "C:\test.xls", True, ""

This keeps the formatting from the query but i cannot append more than one worksheet to the excel file without overwriting the previous entry

Regards, G

 

[Call_Me_Sam]

Hi Again, thanks for your endeavours. What i did find was that the following code gives my desired effect but not completely:

DoCmd.OutputTo acOutputQuery, "Query", "MicrosoftExcel(*.xls)", "C:\test.xls", True, ""

This keeps the formatting from the query but i cannot append more than one worksheet to the excel file without overwriting the previous entry

Regards, G

so we need a mix of both...is it safe to mix DoCmd's..??

 

[grahamsyuk]

OutputTo

hi i have no idea, im getting out of my depth here

If only if it was so simple as to use the following code to export queries tospecific worksheets within an excel file

DoCmd.OutputTo acOutputQuery, "Query", "MicrosoftExcel(*.xls)", "C:\test.xls", True, ""

Thanks for your help, G

 

[chergh]

The issue here are the crappy "OutputTo" and "TransferSpreadsheet" methods. These can't do what you want. So let me introduce you to the "copyfromrecordset" method. You will neeed to first add a reference to the MS excel object library and you want your code to be something like this:

sub bleh ()

dim xlApp as excel.application
dim wb as excel.workbook
dim ws as excel.worksheet

dim path as string

dim rs as recordset
dim db as database

set db = currentdb

path = "C:\test.xls"

Set xlapp = new excel.application
set wb = xlapp.workbooks.open(path)

'Here you need to know how many worksheets you need

do until wb.worksheets.count = <your number of queries here>
   wb.worksheets.add
loop

set rs = db.openrecordset("QueryName1")

wb.worksheets(1).range("A1").copyfromrecordset rs

set rs = nothing 

set rs = db.openrecordset("QueryName2")

wb.worksheets(2).range("A1").copyfromrecordset rs

set rs = nothing

set rs = db.openrecordset("QueryName3")

wb.worksheets(3).range("A1").copyfromrecordset rs

end sub

Now this is quite crude but should illustrate how to do it in principle. Let me know if you have any problems, I've not tested the code so there may be a couple of mistakes in it.

 

[Call_Me_Sam]

The issue here are the crappy "OutputTo" and "TransferSpreadsheet" methods. These can't do what you want. So let me introduce you to the "copyfromrecordset" method. You will neeed to first add a reference to the MS excel object library and you want your code to be something like this:

sub bleh ()
 
dim xlApp as excel.application
dim wb as excel.workbook
dim ws as excel.worksheet
 
dim path as string
 
dim rs as recordset
dim db as database
 
set db = currentdb
 
path = "C:\test.xls"
 
Set xlapp = new excel.application
set wb = xlapp.workbooks.open(path)
 
'Here you need to know how many worksheets you need
 
do until wb.worksheets.count = <your number of queries here>
   wb.worksheets.add
loop
 
set rs = db.openrecordset("QueryName1")
 
wb.worksheets(1).range("A1").copyfromrecordset rs
 
set rs = nothing 
 
set rs = db.openrecordset("QueryName2")
 
wb.worksheets(2).range("A1").copyfromrecordset rs
 
set rs = nothing
 
set rs = db.openrecordset("QueryName3")
 
wb.worksheets(3).range("A1").copyfromrecordset rs
 
end sub

Now this is quite crude but should illustrate how to do it in principle. Let me know if you have any problems, I've not tested the code so there may be a couple of mistakes in it.

chergh, this is great, i apdated it slightly to accomodate if the file wasn't already created..but i am still losing the % formatting i put the table?

 

[chergh]

You can just set the format in excel so if your percentages are in column C then you would format it like:

wb.worksheets(1).columns("C").style = "Percent"

 

[Call_Me_Sam]

You can just set the format in excel so if your percentages are in column C then you would format it like:

wb.worksheets(1).columns("C").style = "Percent"

good one!! like it...so set the formatting afterwards..

 

[Call_Me_Sam]

see wht happens when you do mix you're DoCmd's..you get to use CopyFromRecordsets

 

[chergh]

Yeah I always do formatting at a last step.

 

[Call_Me_Sam]

guess my trouble is i don't need to worry about formatting as i then have formulae and pivot table spicking up the data once in excel... so i make do with raw numbers and text

 

[grahamsyuk]

Hi Guys, thanks very very much for your support. I will be getting stuck into this again later today. thanks again, G

 

[Call_Me_Sam]

Hi Guys, thanks very very much for your support. I will be getting stuck into this again later today. thanks again, G

anytime, just glad chergh jumped in else we may have been in a right state of mixing functions..

 

[grahamsyuk]

hi Sam, how did you do the check for the excel file being present in the directory? if not i want to create the file

regards, G

 

[Call_Me_Sam]

hi Sam, how did you do the check for the excel file being present in the directory? if not i want to create the file

regards, G

I use the Dir function, here's a snippet of how i check

'Declares variables
Dim dirName As String
Dim strCheck As String ' filename that needs checking for
 
'Checks if directory exists by searching for text file
'strPath is the directory path, strFile is the specific filename
strCheck = strPath & strFile
 
'Dir will return the filename if the file exists
dirName = dir(strCheck, vbHidden)
 
'So here we check dirName against file name
If dirName <> strFile Then
   'the file doesn't exist and will need creating
else
  'the does exist, use it
End If

do a helpfile search for more info on the Dir function, i'm prob not that clear. But this is the way i do it most times..

 

[danielle.EWAIG]

Hi,

I have used Chergh's coding to solve this same issue I was having with exporting the filtered data from multiple subforms. However, I am getting a runtime error of "3061: Too few parameters. Expected 1." on the vba line:

set rs = db.openrecordset("QueryName1")

My format follows this example exactly; am I missing something?


My code is below, for your reference:

Private Sub ExportButton_Click()
Dim xlApp As excel.Application
Dim wb As excel.Workbook
Dim ws As excel.Worksheet
 
Dim path As String
 
Dim rs As Recordset
Dim db As Database
 
Set db = CurrentDb
 
path = "C:\Reports\UData.xls"
 
Set xlApp = New excel.Application
Set wb = xlApp.Workbooks.Open(path)
 
 
Do Until wb.Worksheets.Count = 2
   wb.Worksheets.Add
Loop
 
Set rs = db.OpenRecordset("CQuery")
wb.Worksheets(1).Range("A1").CopyFromRecordset rs
 
Set rs = Nothing
 
Set rs = db.OpenRecordset("SQuery")
 
wb.Worksheets(2).Range("A1").CopyFromRecordset rs
 
Set rs = Nothing
End Sub

Any help is greatly appreciated!

 

[Brianwarnock]

Does this help

http://www.pcreview.co.uk/forums/openrecordset-failed-open-query-run-time-error-3061-a-t1638724.html

Brian