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
Err.Clear
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
rst.MoveFirst
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
rst.MoveNext
Set xlc = xlc.Offset(1, 0)
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
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,
Manu
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
Err.Clear
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
rst.MoveFirst
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
rst.MoveNext
Set xlc = xlc.Offset(1, 0)
Loop
End If
rst.Close
Set rst = Nothing
dbs.Close
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,
Manu
Last edited: