Export data to multiple Excel files using Template

Discussion in 'Microsoft Access External Data' started by manu-m, Jul 13, 2012.

  1. manu-m

    manu-m

    Joined:
    Jul 13, 2012
    Messages:
    1
    Likes Received:
    0
    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
     
    Last edited: Jul 13, 2012
    manu-m, Jul 13, 2012
    #1
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. jubejoie@aol.com

    EXPORT EXPORT EXPORT

    jubejoie@aol.com, May 11, 2004, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    543
    Douglas J. Steele
    May 12, 2004
  2. sunilkeswani@gmail.com

    URGENT HELP! Export to Excel Template multiple cells

    sunilkeswani@gmail.com, Nov 3, 2005, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    522
    Guest
    Nov 7, 2005
  3. JudyU

    Access Export of data to Excel using VBA - data looks awful

    JudyU, Apr 14, 2008, in forum: Microsoft Access External Data
    Replies:
    4
    Views:
    999
    JudyU
    Apr 16, 2008
  4. efandango

    Create an export file using user generated title and export data t

    efandango, Oct 4, 2008, in forum: Microsoft Access External Data
    Replies:
    6
    Views:
    538
    Douglas J. Steele
    Oct 5, 2008
  5. neilap

    Export Data from an access table to a pre-formatted excel template

    neilap, Nov 17, 2009, in forum: Microsoft Access External Data
    Replies:
    1
    Views:
    2,275
    Pendragon
    Nov 17, 2009
Loading...

Share This Page