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

Peter_W

New Member
Joined
Apr 2, 2018
Messages
29
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 a moderator:

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,845
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:

Peter_W

New Member
Joined
Apr 2, 2018
Messages
29
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.
 

Peter_W

New Member
Joined
Apr 2, 2018
Messages
29
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.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,845
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 :wink:
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:

Peter_W

New Member
Joined
Apr 2, 2018
Messages
29
Doesn't work doesn't help, or so my signature says :wink:
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.
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
820
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:

Peter_W

New Member
Joined
Apr 2, 2018
Messages
29
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.
 

Peter_W

New Member
Joined
Apr 2, 2018
Messages
29
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!
 

welshgasman

Well-known Member
Joined
May 25, 2013
Messages
820
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.?
 

Watch MrExcel Video

Forum statistics

Threads
1,101,774
Messages
5,482,850
Members
407,366
Latest member
Rofsal

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top