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

[Peter_W]

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.

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

 

[Micron]

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.

 

[Peter_W]

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]

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]

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.

 

[Peter_W]

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]

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?

 

[Peter_W]

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]

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]

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.?