Results 1 to 10 of 10

Thread: Need to export data to existing Excel file, using variable in the file name
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 Need to export data to existing Excel file, using variable in the file name

    Hello all,
    My Access 2010 database, using DAO formats, needs to export data to an existing Excel spreadsheet.

    I found some VBA code which works great, if I give it a single clear-cut file name, but I have numerous projects in the database, and depending on which one is selected by a form, the d/b needs to export one out of potential many. Importantly, I need to send the data to an existing Excel file.

    I have tried dimming a variable of ProjExport, but when I place it in the filename, I get errors, like syntax, etc. depending on different attempts.

    Would anyone know how to correctly place a variable inside the file name? I using the same ProjExport
    variable also, to depict a recordset, which would be the Access table of data I am exporting.

    I am totally lost. Hopefully, this might be an easy one for an expert, thanks.


    Code:
    Private Sub Click_For_Excel_File_Export_Click()
    
    Dim oExcel As Excel.Application
    Dim oWorkBook As Excel.Workbook
    Dim oWorksheet As Excel.Worksheet
    Dim ProjFileName As String
    
    
    
    'There are numerous projects kept inside the database, whcih can be selected by a form:
    
    
    
    Dim ProjExport As String
    Dim sTbl2 As String
        sTbl2 = Nz(Me.ExistingProjNum, "")
        If sTbl2 <> "" Then
        ProjExport = Me.ExistingProjNum
             
        ElseIf Me.NewProjNum <> "" Then
        ProjExport = Me.NewProjNum
        End If
        
    'Would like to use a variable in the Excel workbook name, since there are numerous projects:
    
    
    
    Set oWorkBook = oExcel.Workbooks.Open(Application.CurrentProject.Path \ "ProjExport & .xlsm")
    'The specified worksheet to store project data
    
    
    Set oWorksheet = oWorkBook.Worksheets("TotalHistory")
    
    'The specified table which relates to the selected project:
    
    
    
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("ProjExport")
    'The specified table
    oWorksheet.Range("A5").CopyFromRecordset rs
    
    rs.Close
    
    
    oWorkBook.Save
    oWorkBook.Close
    oExcel.Quit
    
    
    
    Set rs = Nothing
    Set oWorksheet = Nothing
    Set oWorkBook = Nothing
    Set oExcel = Nothing
    
    
    End Sub
    Last edited by xenou; Jul 15th, 2019 at 12:06 PM. Reason: Add Code Tags

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

    Default Re: Need to export data to existing Excel file, using variable in the file name

    what jumps out is
    - you don't wrap string variables (their names) in quotes, so not "ProjExport & .xlsm" but ProjExport & ".xlsm"
    - there is a space where you probably don't have one, so please use code tags (menu #) with indentation to make your code easier to read, but especially to prevent us from pointing out issues that you don't have. See the CurrentProject.Path \ "ProjExport part some forums inject spaces at around 50 characters and nothing you do short of using code tags will stop that and other issues.
    - if the method you use needs the entire path enclosed in quotes, that is an additional issue, but let's fix what's obvious first.
    Last edited by Micron; Jul 14th, 2019 at 07:08 PM.
    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."

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

    Default Re: Need to export data to existing Excel file, using variable in the file name

    Quote Originally Posted by Micron View Post
    what jumps out is
    - you don't wrap string variables (their names) in quotes, so not "ProjExport & .xlsm" but ProjExport & ".xlsm"
    - there is a space where you probably don't have one, so please use code tags (menu #) with indentation to make your code easier to read, but especially to prevent us from pointing out issues that you don't have. See the CurrentProject.Path \ "ProjExport part some forums inject spaces at around 50 characters and nothing you do short of using code tags will stop that and other issues.
    - if the method you use needs the entire path enclosed in quotes, that is an additional issue, but let's fix what's obvious first.
    Thank you Micron. Unfortunately, the approach of using ProjExport & ".xlsm", for the file name, does not work either. The code fails on that one line, with an error of: Run Time Error '13' type mismatch

    I'm not sure how to do "code tags". I will study how to do that next time. Thanks again, but no luck yet.

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

    Default Re: Need to export data to existing Excel file, using variable in the file name

    Quote Originally Posted by Peter_W View Post
    Thank you Micron. Unfortunately, the approach of using ProjExport & ".xlsm", for the file name, does not work either. The code fails on that one line, with an error of: Run Time Error '13' type mismatch

    I'm not sure how to do "code tags". I will study how to do that next time. Thanks again, but no luck yet.
    OK, I found one previous error in the code....it was using Excel.Workbook in the dim and Excel.Workbooks in the file name line. So, making both Excel.Workbooks fixed the type mismatch problem.

    So,now I advance in the sub, but get stopped at this line:

    Set oWorksheet = oWorkBook.Worksheets("TotalHistory")

    That line generates a Compile Error - Method or data member not found. There is definitely an Excel file waiting in the folder, and it definitely has a worksheet named TotalHistory.

    Any thoughts about this error? Thanks in advance.

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

    Default Re: Need to export data to existing Excel file, using variable in the file name

    Unfortunately, the approach of using ProjExport & ".xlsm", for the file name, does not work either.
    Doesn't work doesn't help, or so my signature says
    Posting what you tried is a much bigger help - especially if you copy and paste.
    As mentioned, the code tag button is right on the menu bar when you're composing your post. I would have posted a pic but it seems this forum doesn't allow it.

    We're crossing up our posts. See http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm for lots of great info on what you're doing. I'm a bit rusty on the subject. For Method or data member not found it may be that it's Sheets, not worksheets. That site will make it clear, no doubt.

    it was using Excel.Workbook in the dim and Excel.Workbooks in the file name line
    This means you are not requiring variable declaration (vb editor > Options). Research OPTION EXPLICIT , get it set permanently and add it manually where you do not have it, then compile your code. You might end up with a lot of errors not found up to this point.
    Last edited by Micron; Jul 14th, 2019 at 09:45 PM. Reason: added info
    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."

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

    Default Re: Need to export data to existing Excel file, using variable in the file name

    Quote Originally Posted by Micron View Post
    Doesn't work doesn't help, or so my signature says
    Posting what you tried is a much bigger help - especially if you copy and paste.
    As mentioned, the code tag button is right on the menu bar when you're composing your post. I would have posted a pic but it seems this forum doesn't allow it.

    We're crossing up our posts. See http://www.accessmvp.com/KDSnell/EXCEL_MainPage.htm for lots of great info on what you're doing. I'm a bit rusty on the subject. For Method or data member not found it may be that it's Sheets, not worksheets. That site will make it clear, no doubt.

    This means you are not requiring variable declaration (vb editor > Options). Research OPTION EXPLICIT , get it set permanently and add it manually where you do not have it, then compile your code. You might end up with a lot of errors not found up to this point.
    Thanks Micron! That Snell doco really looks good. Hopefully my error/solution is in that link. Will check it out, thanks again.

  7. #7
    Board Regular
    Join Date
    May 2013
    Posts
    654
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to export data to existing Excel file, using variable in the file name

    I copied your code and created variables for the form controls and that compiles OK?
    The compiler would not have any idea of worksheet names, so it is not that.?

    Put a breakpoint on where you open the workbook and inspect the variables.
    I suspect nothing is being opened?
    Last edited by welshgasman; Jul 15th, 2019 at 04:08 AM.
    Office 2007
    Access novice. Sometimes trying to give something back

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

    Default Re: Need to export data to existing Excel file, using variable in the file name

    Quote Originally Posted by welshgasman View Post
    I copied your code and created variables for the form controls and that compiles OK?
    The compiler would not have any idea of worksheet names, so it is not that.?

    Put a breakpoint on where you open the workbook and inspect the variables.
    I suspect nothing is being opened?
    Thanks Welshgasman, will give that I try.

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

    Default Re: Need to export data to existing Excel file, using variable in the file name

    All,

    I solved this issue through trial and error. The culprit was this line:


    Application.CurrentPoject.Path & "" & ProjExport & ".xlsm" that is the proper syntax, and then you can use a variable to export to various Excel files.


    And then I need establish the variable after dimming, for example: ProjExport = "ProjectABC", etc. Thanks all for their help!

  10. #10
    Board Regular
    Join Date
    May 2013
    Posts
    654
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to export data to existing Excel file, using variable in the file name

    I don't believe you should have any single quote in there.?
    I would Debug.Print Application.Currentproject.Path and ProjExport and then concatenate them.?
    For a start
    Application.Currentproject.Path does not have a trailing slash on it, so you would need to add one?

    That is the reason for inspecting each variable.?
    Office 2007
    Access novice. Sometimes trying to give something back

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
  •