Export to multiple Excel files using template

ecapox · February 5th, 2008, 10:38 AM

Newly registered here thanks to a friend's advice. My issue is the following...

I have some VBA code that gets a list of taxcodes and creates a crosstab query for each tax code. Each resulting crosstab is exported to an Excel spreadsheet using the taxcode as the name. example 010.xls, 020.xls, etc.

I can do this just fine and the code works great. Where i run into problems is when i attempt to use an Excel template for the exported files. This template contains a macro, and some headings that i need in each Excel spreadsheet that is exported.

With the code below, some strangeness happens. The first loop creates the 010.xls file, using the correct template, but it is blank. It then proceeds to open up multiple other Excel spreadsheets. These are in order of the taxcodes that i was looping through. For example....

When I open up the exported 010.xls file, it contains no data, but it also opens up 0101, 0202, 0303, 0404, etc. Now these "extra" Excel spreadsheets contain the correct data and use the template, but they arent really there! They seem to be some ghost files that are help temporarily because if i close everything, they dont come back.

Here is the code that i currently have running...

Quote:

quote:

Public Sub NewExport()

'Start of code
Dim qdf As DAO.QueryDef
Dim dbs As DAO.Database
Dim rstMgr As DAO.Recordset
Dim strSQL As String

'Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabTwo As Byte = 1
Const cStartRow As Byte = 5
Const cStartColumn As Byte = 1

' Define the query that will be used for exporting
Const strQName As String = "qselExportCompare"

Set dbs = CurrentDb()

' Create tax entity recordset that will be looped through
strSQL = "SELECT DISTINCT sTaxCode FROM dbo_TIFTRFd_Entity;"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbReadOnly)

' Loop through list of entities and export data to excel file by entity
If rst.EOF = False And rst.BOF = False Then
   rst.MoveFirst
   Do While rst.EOF = False

       strSQL = "TRANSFORM Sum(qSelExportFormat.nPYAmount) AS SumOfnPYAmount " _
           & "SELECT qSelExportFormat.sTaxCode, qSelExportFormat.sStateID, Sum(qSelExportFormat.nPYAmount) AS [Total Of nPYAmount] " _
           & "FROM qSelExportFormat " _
           & "WHERE sTaxCode = '" & rst!sTaxCode.Value & "' " _
           & "GROUP BY qSelExportFormat.sTaxCode, qSelExportFormat.sStateID " _
           & "PIVOT qSelExportFormat.[Full Account]; "

       Set qdf = CurrentDb.QueryDefs(strQName)
       qdf.Name = strQName
       qdf.sql = strSQL

       ' Set the locations of the template and the output
       sTemplate = "O:\TECHNOLOGY\TaxWeb\IFTRF\templates\IFTRFTemplat e.xls"
       sOutput = "O:\TECHNOLOGY\TaxWeb\IFTRF\Export\" & rst!sTaxCode.Value & ".xls"
       If Dir(sOutput) <> "" Then Kill sOutput
           FileCopy sTemplate, sOutput

                    ' Create the Excel Applicaiton, Workbook and Worksheet and Database object

                    Set appExcel = Excel.Application
                    Set wbk = appExcel.Workbooks.Open(sOutput)
                    Set wks = appExcel.Worksheets("Compare")
                    'sSQL = "select * from qrySales"
                    Set dbs = CurrentDb
                    Set erst = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

                    ' For this template, the data must be placed on the 4th row, third column.
                    ' (these values are set to constants for easy future modifications)
                    iCol = cStartColumn
                    iRow = cStartRow
                    If Not erst.BOF Then erst.MoveFirst
                        Do Until erst.EOF
                           iFld = 0
                           lRecords = lRecords + 1
                           'Me.lblMsg.Caption = "Exporting record #" & lRecords & " to SalesOutput.xls"
                           'Me.Repaint

                           For iCol = cStartColumn To cStartColumn + (erst.Fields.Count - 1)
                              wks.Cells(iRow, iCol) = erst.Fields(iFld)

                              'If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
                              ' wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
                              'End If

                              wks.Cells(iRow, iCol).WrapText = False
                              iFld = iFld + 1
                           Next

                           wks.Rows(iRow).EntireRow.AutoFit
                           iRow = iRow + 1

                           erst.MoveNext
                        Loop

                       ' Cleanup all objects (resume next on errors)
                       On Error Resume Next
                       'Set wks = Nothing
                       'Set wbk = Nothing
                       Set appExcel = Nothing
                       Set erst = Nothing
                       Set dbs = Nothing
                       DoCmd.Hourglass False

                    'End If

                    'ExportRequest = "Total of " & lRecords & " rows processed."
                    'Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."
          'End If

       qdf.Close
       Set qdf = Nothing
       rst.MoveNext
   Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.Close
Set dbs = Nothing
'End of code

End Sub

This has to be something simple that i have overlooked because "theoretically" it does what it is supposed to do, just doesnt place the data in the Excel file that is generated.

Any help is GREATLY appreciated.