Dear friends,
i've got the following code for exporting a query to a workbook.
I would like to have another sheet with informations.
The problem is that the book is always recreated and if i use it as a template, all the sheets are always deleted.
Any ideas? I'd like to have 2 sheets, 1 the exported query and the 2nd the information sheet which always will be in the file.
Dim objXl As Object
Dim objActiveWkb As Object
stDocName = "Invoices"
filename = "C:\Temp" & "\" & stDocName
DoCmd.OutputTo acQuery, stDocName, acFormatXLSX, filename, , "Invoices.xlsx", , acExportQualityPrint 'Transfer is not working, i always get doublivated sheet
Set objXl = CreateObject("Excel.Application")
Set objActiveWkb = objXl.Workbooks.Open("c:\temp\Invoices.xlsx")
With objActiveWkb.Sheets("Invoices") 'Formatting workbook
.Rows("1:1").Font.Bold = True
.Columns("A:A").ColumnWidth = 30
.Columns("A:A").Font.Bold = True
.Columns("A:A").Font.Color = vbRed
.Columns("B:B").ColumnWidth = 12
.Columns("B:B").HorizontalAlignment = xlCenter
.Columns("c:c").ColumnWidth = 23
.Columns("d:d").ColumnWidth = 60
.Columns("e:e").ColumnWidth = 20
.Columns("f:f").ColumnWidth = 10
.Columns("f:f").HorizontalAlignment = xlCenter
.Columns("g:g").ColumnWidth = 18
.Columns("h:h").ColumnWidth = 20
.Columns("i:i").ColumnWidth = 20
.Columns("j:j").ColumnWidth = 18
.Columns("j:j").HorizontalAlignment = xlCenter
.Columns("k:k").ColumnWidth = 20
End With
objActiveWkb.Close SaveChanges:=True
Set objActiveWkb = Nothing
Set objXl = Nothing
Thank you in advance!!!!
i've got the following code for exporting a query to a workbook.
I would like to have another sheet with informations.
The problem is that the book is always recreated and if i use it as a template, all the sheets are always deleted.
Any ideas? I'd like to have 2 sheets, 1 the exported query and the 2nd the information sheet which always will be in the file.
Dim objXl As Object
Dim objActiveWkb As Object
stDocName = "Invoices"
filename = "C:\Temp" & "\" & stDocName
DoCmd.OutputTo acQuery, stDocName, acFormatXLSX, filename, , "Invoices.xlsx", , acExportQualityPrint 'Transfer is not working, i always get doublivated sheet
Set objXl = CreateObject("Excel.Application")
Set objActiveWkb = objXl.Workbooks.Open("c:\temp\Invoices.xlsx")
With objActiveWkb.Sheets("Invoices") 'Formatting workbook
.Rows("1:1").Font.Bold = True
.Columns("A:A").ColumnWidth = 30
.Columns("A:A").Font.Bold = True
.Columns("A:A").Font.Color = vbRed
.Columns("B:B").ColumnWidth = 12
.Columns("B:B").HorizontalAlignment = xlCenter
.Columns("c:c").ColumnWidth = 23
.Columns("d:d").ColumnWidth = 60
.Columns("e:e").ColumnWidth = 20
.Columns("f:f").ColumnWidth = 10
.Columns("f:f").HorizontalAlignment = xlCenter
.Columns("g:g").ColumnWidth = 18
.Columns("h:h").ColumnWidth = 20
.Columns("i:i").ColumnWidth = 20
.Columns("j:j").ColumnWidth = 18
.Columns("j:j").HorizontalAlignment = xlCenter
.Columns("k:k").ColumnWidth = 20
End With
objActiveWkb.Close SaveChanges:=True
Set objActiveWkb = Nothing
Set objXl = Nothing
Thank you in advance!!!!