Exporting multiple Excel spreadhsheets using Excel templates

  • Thread starter ecapox via AccessMonster.com
  • Start date
E

ecapox via AccessMonster.com

My issue is this....

I have the current code below that works great and does everything i want it
to do...with one exception. When it exports these queries to Excel
spreadsheets, i would like it to use an Excel template i have set up. I would
like that because the Excel template contains some code that the users of the
spreadsheets will need to use, and it contains a few formatting issues they
need. Also, since i will be using a template, i need to tell my code to isert
the query results beginning on line 4 of each spreadsheet...not line 1 as it
currently does.


Your help is greatly appreciated.

Now for the code....





Public Sub Export()

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

' 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
qdf.Close
Set qdf = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExce9, _
strQName, "O:\TECHNOLOGY\TaxWeb\IFTRF\Export\" & rst!sTaxCode.
Value & ".xls"

rst.MoveNext
Loop
End If

rstMgr.Close
Set rstMgr = Nothing

dbs.Close
Set dbs = Nothing
'End of code

End Sub
 
E

ecapox via AccessMonster.com

While this seems like it gives me TOTAL control, i'd like to keep this as
simple as possible. With that, I have full understanding that i might not get
"simple" because what im asking for isnt "simple", but this seems overly
complex. Either way, I'll play with it in case this is my only option, but
something more simple is preferable.

Thanks!
Emanuele

Alex said:
Hi,
instead of DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExce9 you
have to open recordset and then put values for each field and each record
into opened excel template. Look here:
http://www.mvps.org/access/modules/mdl0006.htm

takes more time to write, but you have 100% of control
My issue is this....
Click to expand...
[quoted text clipped - 73 lines]
End Sub
Click to expand...
Click to expand...
 
A

Alex Dybenko

Hi!
Believe me, it looks complex, but once you start to use it - you will find
it simple in compare to changing something in DoCmd.TransferSpreadsheet
result

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


ecapox via AccessMonster.com said:
While this seems like it gives me TOTAL control, i'd like to keep this as
simple as possible. With that, I have full understanding that i might not
get
"simple" because what im asking for isnt "simple", but this seems overly
complex. Either way, I'll play with it in case this is my only option, but
something more simple is preferable.

Thanks!
Emanuele

Alex said:
Hi,
instead of DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExce9 you
have to open recordset and then put values for each field and each record
into opened excel template. Look here:
http://www.mvps.org/access/modules/mdl0006.htm

takes more time to write, but you have 100% of control
My issue is this....
Click to expand...
[quoted text clipped - 73 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
E

ecapox via AccessMonster.com

Ok, I'll start tailoring it to my process this morning after my 10:00 meeting.
I will SURELY have questions!

Thanks,
Emanuele

Alex said:
Hi!
Believe me, it looks complex, but once you start to use it - you will find
it simple in compare to changing something in DoCmd.TransferSpreadsheet
result
While this seems like it gives me TOTAL control, i'd like to keep this as
simple as possible. With that, I have full understanding that i might not
Click to expand...
[quoted text clipped - 19 lines]
Click to expand...
 
E

ecapox via AccessMonster.com

Ok, not too sure how this works. All the references in the Sub sTestXL refer
to opening workbooks and active workbooks....but this is in Access. When ran,
the sub opens up a message box with "hello world". It doesnt open excel or
anything.

Im lost.
Ok, I'll start tailoring it to my process this morning after my 10:00 meeting.
I will SURELY have questions!

Thanks,
Emanuele
Hi!
Believe me, it looks complex, but once you start to use it - you will find
Click to expand...
[quoted text clipped - 6 lines]
Click to expand...
 
E

ecapox via AccessMonster.com

Now that i look at this more closely, it doesnt make any sense....

The first IF statement:

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

Doesnt do anything. There is no verification of ifsAppRunning("Excel") = ???
No matter if Excel is open or not, it is always going to do the first IF.
Basically the only thing that works here is the msgbox at the bottom of the
code.

Ok, not too sure how this works. All the references in the Sub sTestXL refer
to opening workbooks and active workbooks....but this is in Access. When ran,
the sub opens up a message box with "hello world". It doesnt open excel or
anything.

Im lost.
Ok, I'll start tailoring it to my process this morning after my 10:00 meeting.
I will SURELY have questions!
Click to expand...
[quoted text clipped - 7 lines]
Click to expand...
 
A

Alex Dybenko

Hi,
ok, but this is a sample. Instead of objXL.Application.workbooks.Add
you need to open your template.and then write certain data to certain cells
using .Worksheets(1).Cells(1, 1)
You can start macro recorder in excel and then do required tasks to find out
the code you need to run in VBA

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

ecapox via AccessMonster.com said:
Ok, not too sure how this works. All the references in the Sub sTestXL
refer
to opening workbooks and active workbooks....but this is in Access. When
ran,
the sub opens up a message box with "hello world". It doesnt open excel or
anything.

Im lost.
Ok, I'll start tailoring it to my process this morning after my 10:00
meeting.
I will SURELY have questions!

Thanks,
Emanuele
Hi!
Believe me, it looks complex, but once you start to use it - you will
find
Click to expand...
[quoted text clipped - 6 lines]
End Sub
Click to expand...
Click to expand...
Click to expand...
 
E

ecapox via AccessMonster.com

So this is what i have now.....



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)

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


' 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

' 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 rst = 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 rst.BOF Then rst.MoveFirst
Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
'Me.lblMsg.Caption = "Exporting record #" & lRecords & "
to SalesOutput.xls"
'Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.
Count - 1)
wks.Cells(iRow, iCol) = rst.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
rst.MoveNext
Loop

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


'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




Funny thing happens.... The output, there is a sheet called 010.xls, but when
i open it up by clicking on it, it opens up 2 excel spreadsheets. 010.xls
contains no data, and 0101 contains data. 0101 is not denoted as 0101.xls,
just 0101. If i open up Excel and navigagte to 010.xls, the second 0101
window does not show up. 0101 contains EXACTLY what i need.

Strange....




Alex said:
Hi,
ok, but this is a sample. Instead of objXL.Application.workbooks.Add
you need to open your template.and then write certain data to certain cells
using .Worksheets(1).Cells(1, 1)
You can start macro recorder in excel and then do required tasks to find out
the code you need to run in VBA
Ok, not too sure how this works. All the references in the Sub sTestXL
refer
Click to expand...
[quoted text clipped - 18 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Code Help 3
Export table to multiple excel worksheets` 7
export vba not working 0
Ken Snell's Multiple sheet export 2
Passing Parameter From Form mismatched criteria 10
Exporting multiple files to a template instead of a new file! 5
return without gosub error 0
return without gosub error 0

Top