Saving formated Access Report as object Workbook Excel. Version: 97 (

[syoung4]

I am trying to export a large query to excel and be able to group each contract and put on to a separate worksheet the same as a report would do. As their are many contracts it is not possible to be able to define each contract to go on to a named worksheet.

I need to be able to somehow transfer a formatted report with logo to an Excel work book and open the open save dialog box to give the Excel file a name.

Does anyone know how I could do this?


Regards,

Sid.

 

[smedvid]

IMHO, you will need to create an Excel Object via VBA Code... It can be pretty involved..

code snippets...

Dim wbkNewbk As Excel.Workbook
Dim wksNew As Excel.Worksheet
Dim appExcel As New Excel.Application

...
Set wksNew = wbkNewbk.Worksheets.Add
wksNew.Name = "Test_" & lcYear & "_" & lcMonth
...


'Write to Cells.. Here
Do While Not rsEmployeeHeader.EOF 'Outer Control Loop - Employee Loop
lnColCnt = 1
With wks

If llArchiveFlag = False Then
.Cells(lnProcessRow, lnColCnt) = "Statement"
Else
.Cells(lnProcessRow, lnColCnt) = "Statement (Archive)"
End If
.Cells(lnProcessRow, lnColCnt).Font.Bold = True
.Cells(lnProcessRow, lnColCnt).Font.Size = 16

lcStartRange = "" & Chr(64 + lnColCnt) & lnProcessRow & ""
lcEndRange = "" & Chr(64 + lnColCnt + 4) & lnProcessRow & ""
lcRange = lcStartRange & ":" & lcEndRange

.Range(lcRange).HorizontalAlignment = xlLeft
.Range(lcRange).VerticalAlignment = xlBottom
.Range(lcRange).MergeCells = True

lnProcessRow = lnProcessRow + 1

.Cells(lnProcessRow, lnColCnt) = "'" & lcTitle
.Cells(lnProcessRow, lnColCnt).Font.Size = 12
lnProcessRow = lnProcessRow + 2

lnStartBorderCol = lnColCnt
lnStartBorderRow = lnProcessRow



...
With wksNew
.Cells.Select
.Cells.EntireColumn.AutoFit
End With
...

'<< Save Excel File >>
wksNew.SaveAs FileName:=lcFileName, _
FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

appExcel.Workbooks(wbkNewbk.Name).Close
Set wksNew = Nothing
Set wbkNewbk = Nothing


Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[syoung4]

Thank you for your reply.
I am getting Compile error Variable not defined on all the
Variables. I am trying to work out how to do my own.

lcYear
lcMonth
rsEmployeeHeader
lnColCnt
wks
llArchiveFlag
lnProcessRow

Are the variables from a report?
Can I use a report as a recordset for this?

Regards,
Sid.