PC Review

Thread Tools Rate Thread

Export data to multiple Excel files using Template

New Member
Join Date: Jul 2012
Posts: 1
      13th Jul 2012
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 by manu-m; 13th Jul 2012 at 07:39 AM.. Reason: alignment
Reply With Quote

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
export multiple sheets to multiple excel files Tanya Microsoft Excel Misc 1 20th Apr 2009 08:57 PM
Export to excel in asp.net using excel template Grey Microsoft ASP .NET 4 17th Oct 2007 09:08 AM
export excel data to a word template using a macro =?Utf-8?B?Um9zczE=?= Microsoft Excel Programming 1 25th Mar 2005 05:31 AM
EXPORT EXPORT EXPORT jubejoie@aol.com Microsoft Access External Data 1 12th May 2004 12:05 AM
Re: i cant export wab files/// files->export is disable neo [mvp outlook] Microsoft Outlook 1 14th Jul 2003 01:11 PM




All times are GMT +1. The time now is 04:58 AM.