Export data to multiple Excel files using Template

Jul 13, 2012
Reaction score
I have a data where it has product codes with quantities in different sales office codes :

e.g : prodcode -- 5001 -- 5002 -- 5003 -- 5004 -- 5010 ....... etc
-------13148171 ----- 200 ------150 ------ 60 ---------80 --------175
---------28126676 ------55 --------95 ------110 --------145 ----------70

I have a template file in which I need to place one row values (eg. 13148171) based on prodcode and save it as the prodcode+.xls.

I give below the code where in I could transfer all datas to one excel file - please help in the code to create multiple excel files using the template file.

Code :

Private Sub Command6_Click()
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean

blnEXCEL = False
blnHeaderRow = True

On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
On Error GoTo 0

xlx.Visible = True

strInitialDirectory = "D:\ALLOC\"

Set xlw = xlx.Workbooks.Open("D:\ALLOC\Copy of Solver.xls")

Set xls = xlw.Worksheets("Template")

Set xlc = xls.Range("B21") ' this is the first cell into which data go

Set dbs = CurrentDb()

Set rst = dbs.OpenRecordset("alloc", dbOpenDynaset, dbReadOnly)

If rst.BOF = False Then

If blnHeaderRow = False Then
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Name
Next lngColumn
Set xlc = xlc.Offset(1, 0)
End If

Do While rst.BOF = False
For lngColumn = 0 To rst.Fields.Count - 1
xlc.Offset(0, lngColumn).Value = rst.Fields(lngColumn).Value
Next lngColumn
Set xlc = xlc.Offset(1, 0)
End If

Set rst = Nothing
Set dbs = Nothing

Set xlc = Nothing
Set xls = Nothing
xlw.SaveAs "prodcode.xls"
xlw.Close False
Set xlw = Nothing
If blnEXCEL = True Then xlx.Quit
Set xlx = Nothing

End Sub

With the above code I could export all data to the particular field in an excel file - whereas I need to send each row value, to a particular field in the template and save it as a different file, with the prodcode value as filename.

awaiting your kind response,

Last edited:

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