Using variable instead of single file name for export to Excel, based on a form selection
Results 1 to 3 of 3

Thread: Using variable instead of single file name for export to Excel, based on a form selection
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    28
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Using variable instead of single file name for export to Excel, based on a form selection

    Hello all,

    There are various subs on the internet which export to an existing Excel file I have tried, and they work fine. But each one I've seen requires the use of a single file name such as SingleFileName.xls

    Since my database had dozens of tables, which might be needed by dozens of related existing Excel files, I am trying to have a form button determine a variable string, which in turn will determine which single table in the d/b, and which single Excel file to use.

    Every attempt I've tried has failed. For the code attached, I tried using a variable called ProjectID. But VBA errors out saying Compile Error - Object required. I totally lost on what that means. Seems like a variable should work for this code. Would any one know how to use a variable both the Excel file to export to, and use that same variable for the Access table in the d/b? Thank you for any responses.

    Again the code works fine if I input a single file name, with a single table name. So the code works fine that way.





    Sub ExcelExportFromAccess()


    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
    Dim ProjectID As String
    Set ProjectID = "Project123456"


    blnEXCEL = False
    ' Replace True with False if you do not want the first row of
    ' the worksheet to be a header row (the names of the fields
    ' from the recordset)

    blnHeaderRow = True

    ' Establish an EXCEL application object

    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


    ' Change True to False if you do not want the workbook to be
    ' visible when the code is running
    xlx.Visible = True


    ' Replace C:\Filename.xls with the actual path and filename
    ' of the EXCEL file into which you will write the data


    Set xlw = xlx.Workbooks.Open("C:\Users\pweston\Documents\Access Test\ProjectID & .xlsm")
    ' Replace WorksheetName with the actual name of the worksheet
    ' in the EXCEL file
    ' (note that the worksheet must already be in the EXCEL file)
    Set xls = xlw.Worksheets("TotalHistory")
    ' Replace A1 with the cell reference into which the first data value
    ' is to be written
    Set xlc = xls.Range("A1") ' this is the first cell into which data go
    Set dbs = CurrentDb()
    ' Replace QueryOrTableName with the real name of the table or query
    ' whose data are to be written into the worksheet
    Set rst = dbs.OpenRecordset(ProjectID, dbOpenDynaset, dbReadOnly)


    If rst.EOF = False And rst.BOF = False Then
    rst.MoveFirst
    If blnHeaderRow = True 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
    ' write data to worksheet
    Do While rst.EOF = 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

    End Sub
    0 0
     

  2. #2
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,664
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using variable instead of single file name for export to Excel, based on a form selection

    why not just continue your prior post for the same issue?
    https://www.mrexcel.com/forum/micros...file-name.html

    The answer I gave before (where I said please use code tags) probably still applies to what I see here but I'm not going to really study your code to be sure.
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."
    0 0
     

  3. #3
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,422
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using variable instead of single file name for export to Excel, based on a form selection

    You probably should continue in the same thread, so I will close this one. Put your updated code there.

    However, this again looks wrong, for the same reasons as before.
    Code:
    Set xlw = xlx.Workbooks.Open("C:\Users\pweston\Documents\Access Test\ProjectID & .xlsm")

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau
    0 0
     

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •