export query - vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi

I want to export my query to a *.txt or *.csv file using a button on the Switchboard. I wish to add an additional few lines to the text file to enable background information of query extract, namely: title, parameters & export date

How do i do this? Does anyone have any example codes i can start fiddlign with

Thanks
Marcus
 
I have this, but it seems to work very very very slowly at generating the report. How do i spend this up? Or is there another way

Function Macro1(
On Error GoTo Macro1_Er

' runs query to create table for export to exce
DoCmd.OpenQuery "Query3", acNormal, acEdi

'(this is very slow - and much slower than running the query manually)
'(not sure how to turn off warning msg's about overwriting table etc...)

' exports Query3
DoCmd.OutputTo acOutputTable, "QueryTable3", acFormatXLS, "20050405_Query3.xls", Fals

Macro1_Exit
Exit Functio

Macro1_Err
MsgBox Error
Resume Macro1_Exi

End Functio


I also have the following macro which formats the excel file (currently operates within excel). I would like the access macro to open excel and do this

Sub FormatExcel(


Workbooks.Open Filename:=
"C:\Documents and Settings\My Documents\20050405_Query3.xls

Rows("1:4").Insert Shift:=xlDow
Range("A1") = "---------------- Title of Report --------------------
Range("A2") = "Report Run @:
Range("B2") = Now(
Range("B2").NumberFormat = "d mmm yy h:mm
With Range("A5", Range("A5").End(xlToRight)) '.Selec
'With Selectio
.HorizontalAlignment = xlCente
.VerticalAlignment = xlCente
.WrapText = Tru
End Wit
Rows("5:5").EntireRow.AutoFi
Columns("C:AM").EntireColumn.AutoFi
Columns("AG:AG").Font.Bold = True 'total
Columns("AI:AI").Font.Bold = True 'total
Columns("AH:AH").Font.Bold = True 'total

With ActiveSheet.PageSetu
.LeftHeader = "&8&F / &A
.CenterHeader = "&8&P / &N
.RightHeader = "&8Printed: &D &T
.RightFooter = "&8Author
.LeftMargin = Application.InchesToPoints(0.393700787401575
.RightMargin = Application.InchesToPoints(0.393700787401575
.TopMargin = Application.InchesToPoints(0.50055
.BottomMargin = Application.InchesToPoints(0.5055
.HeaderMargin = Application.InchesToPoints(0.27559
.FooterMargin = Application.InchesToPoints(0.27559
.FirstPageNumber = xlAutomati
.Order = xlDownThenOve

.PrintTitleRows = "$5:$5
.Orientation = xlLandscap
.FitToPagesWide =
.FitToPagesTall = Fals
.PaperSize = xlPaperA

End Wit

Range("A1").Selec
ActiveWorkbook.Sav

End Su

----- marcus. wrote: ----

Hi

I want to export my query to a *.txt or *.csv file using a button on the Switchboard. I wish to add an additional few lines to the text file to enable background information of query extract, namely: title, parameters & export date

How do i do this? Does anyone have any example codes i can start fiddlign with

Thanks
Marcus
 
Hi Marcus,

With a little change you can use the excel macro in the acess,

I sugest the following changes in the Sub FormatExcel:


------------------------------------

Sub FormatExcel()

Dim objExcel As Object

Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open FILENAME:="C:\Documents and Settings\My
Documents\20050405_Query3.xls"

With objExcel

.Rows("1:4").Insert Shift:=xlDown
.Range("A1") = "---------------- Title of Report
--------------------"
.Range("A2") = "Report Run @:"
.Range("B2") = Now()
. Range("B2").NumberFormat = "d mmm yy h:mm"
With .Range("A5", Range("A5").End(xlToRight)) '.Select
'With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With
.Rows("5:5").EntireRow.AutoFit
.Columns("C:AM").EntireColumn.AutoFit
.Columns("AG:AG").Font.Bold = True 'totals
.Columns("AI:AI").Font.Bold = True 'totals
.Columns("AH:AH").Font.Bold = True 'totals

With .ActiveSheet.PageSetup
.LeftHeader = "&8&F / &A"
.CenterHeader = "&8&P / &N"
.RightHeader = "&8Printed: &D &T"
.RightFooter = "&8Author"
.LeftMargin = Application.InchesToPoints(0.393700787401575)
.RightMargin = Application.InchesToPoints(0.393700787401575)
.TopMargin = Application.InchesToPoints(0.50055)
.BottomMargin = Application.InchesToPoints(0.5055)
.HeaderMargin = Application.InchesToPoints(0.27559)
.FooterMargin = Application.InchesToPoints(0.27559)
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver

.PrintTitleRows = "$5:$5"
.Orientation = xlLandscape
.FitToPagesWide = 1
.FitToPagesTall = False
.PaperSize = xlPaperA3

End With

.Range("A1").Select
.ActiveWorkbook.Save

End With

End Sub
 
Guessing from your comments about warning messages that Query3 is a make table
query. You should be able to export the SELECT query that you are using to
build the make table. GUESSING that Query3 is a maketable query and is using
Query2 as a source. You would end up with code similar to:

DoCmd.OutputTo acOutputQuery, "Query2", acFormatXLS, "20050405_Query3.xls", False

This means that you don't need the DoCmd.OpenQuery line at all and that no table
is created in the database.

If you really need the table to be created, you might try surrounding the
DoCmd.OpenQuery with

DoCmd.SetWarnings False
DoCmd.OpenQuery "Query3", acNormal, acEdit
DoCmd.SetWarnings True

Also, I would make sure to add the line in
DoCmd.SetWarnings True
to your macro1_Err Code or to put it in the Macro1_Exit code.

If you don't you will lose the warnings "Save form? Save changes..." etc. until
you turn warnings back on or you close your application and reopen it.
 
Thanks

John was right about the table - i didn't need to create it. I just thought i was meant to if I wanted excel format output. Obviously not. Is there any reason why it saves in Excel 95? The creating of excel file is still very time consuming, but I've just added a little msg box to "go get some coffee" (probably more like 6 cups!). Is there anyway of adding a progress meter? I know there is a small one at the bottom of the access screen but this seems to get lost in the flipping between screens when I go do something while i'm waiting...

The changes the modify excel macro recommended by B. Biaxo works except for a few lines... i'm not sure why these are different (not working lines have been marked with ' )

With .Range("A5:M5"
' .HorizontalAlignment = xlCente
' .VerticalAlignment = xlCente
.WrapText = Tru
End Wit

and ..

With .ActiveSheet.PageSetu
.LeftHeader = "&8&F / &A
.CenterHeader = "&8&P / &N
.RightHeader = "&8Printed: &D &T
.LeftFooter = "
.CenterFooter = "
.RightFooter = "&8Marcus Saxton : MMS
' .LeftMargin = Application.InchesToPoints(0.393700787401575
' .RightMargin = Application.InchesToPoints(0.393700787401575
' .TopMargin = Application.InchesToPoints(0.50055
' .BottomMargin = Application.InchesToPoints(0.5055
' .HeaderMargin = Application.InchesToPoints(0.27559
' .FooterMargin = Application.InchesToPoints(0.27559
.PrintHeadings = Fals
.PrintGridlines = Fals
' .PrintComments = xlPrintNoComment
.PrintQuality = 60
.CenterHorizontally = Fals
.CenterVertically = Fals
' .Orientation = xlLandscap
.Draft = Fals
' .PaperSize = xlPaperA
.FirstPageNumber = xlAutomati
' .Order = xlDownThenOve
.BlackAndWhite = Fals
.Zoom = Fals
.FitToPagesWide =
.FitToPagesTall = Fals
End Wit

I also note that while it doesn't display excel during this process, it is visible within the 'Task Manager'. It isnt closing excel at the end of the process and so if you try and open the file it won't work. I have to kill excel in the Task Manager to revive excel after this process. Why is this

thanks again for your feed back

Marcus.
 
Funny, I think it should be saving to Excel 97 format if you are using Access97.

And I know of no way to add a progress meter on the Output function. There may
be a way, I just don't know what it is.
 
I'm using Access 2000 to confuse things... so that it would mean even less

Nevermind, its not going to kill anyone

Thanks for your help. I'm stuck on an import *.txt vba query now. I'm sure you will see most post from today

Thanks again
Marcus
 
Marcus,
I also note that while it doesn't display excel during this process, it is visible within the 'Task Manager'. It isnt closing excel at the end of the process and so if you try and open the file it won't work. I have to kill excel in the Task Manager to revive excel after this process. Why is this?
Click to expand...

I forget a line in the end of the code:

++++++++++++++++++++++++++++++++++++++++++
.....
.Range("A1").Select
.ActiveWorkbook.Save
End With

objExcel.Application.Quit '<----------------- The missing
line, this line is necessary to close the Excel

End Sub

++++++++++++++++++++++++++++++++++++++++++
With .Range("A5:M5")
' .HorizontalAlignment = xlCenter
' .VerticalAlignment = xlCenter
.WrapText = True
End With
Click to expand...

About thisI sugest the following changes:

.Range ("A5:M5").Select
With .Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
End With


The others problems you have depends of printer you have in your
system.

You can make this procedure in Excel with the option "Record New
Macro".

Regards

BB
 
Back
Top