variable filepath for output file

[Jaron]

Hi,
I am trying to write a bit of code that will create an export file in a predetermined location, such as the desktop. I don't want the user to have to choose. I just want them to be able to click a command button and export all the tables in my DB to different sheets of the same excel workbook. Currently I have this:

Private Sub Command23_Click()
strPath = "C:\Users\John\Desktop\Exportfile"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table1", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table2", strPath
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table3", strPath
MsgBox "The files have been successfully saved to: 'C:\Users\John\Desktop'", vbOKOnly, ""
End Sub

The problem is, I don't know what the user's login will be, and there may be several different users. I could write a variable, but I'm not sure how, and I'm worried that the variable language may not be universal enough to work across different versions of windows. One idea I had was to find a way to identify the filepath of the open Access application and use the same filepath for the export; that way it doesn't matter because wherever the user has stored the DB, the export will always be right next to it. Does anyone know if that is possible?

[June7]

CurrentProject.Path will return the file path of the database. Run the command in the VBA Immediate Window to see what returns.

Is this a split database and each user has their own working copy of frontend on their computer?

[Jaron]

I'm not sure how I do that. Ctl+G, then paste in the window, then what?

Yes, each user will have their own copy. They will be managing different data in various locations. The idea is, if they run into structure problems, or I just make improvements and want to update their version, they can just export the tables and send me the excel file. Then I can import their data into the new and improved structure, do a little testing, then send the new DB back to them, using the same filename so that they can download and replace existing.

[Jaron]

Ok, I've replaced the filepath with CurrentProject.path as in:

Now I get:
Run-time error '3051':
The Microsoft Access database engine cannot open or write to the file 'C:\Users\[myname]\Desktop'. It is already opened exclusively by another user, or you need permission to view and write its data.

Apparently it is successfully finding the correct path, but it can't write to it. I wonder is that because it's trying to access the DB itself, instead of just it's host file location, or because that file location is locked with user permissions.

I'll give it a try on a different computer and see what happens.

[June7]

Use the VBA Immediate Window to test some code. Type:

?CurrentProject.Path

and see what returns.

Review link at bottom of my post for guidelines on debugging techniques, including use of the Immediate Window.

So while you do this edit for user, they cannot enter new data until you return the file? Unless the design changes involve table structure, a split db would allow users to continue data entry while waiting for your revised frontend. Of course, this does require overhead to manage table links in the frontend.

[Jaron]

You're right. They need access while I'm working. The downtime would only be for the time it takes to import their tables back into the new structure. But it would be nice to do a split DB...i'll have to do some research on that. in the meantime, I still want the export function, if only for back up purposes.
And that being the case, I'm beginning to talk myself out of saving to the same location as the DB; if the back up is needed at all it might be safer elsewhere. So I've done a little looking and from what I've found, this is what I've come up with:

Function GetSaveAsFileName(Optional InitialName As String) As String
     With Application.FileDialog(2) ' msoFileDialogSaveAs
         .InitialFileName = InitialName
         If .Show Then
             GetSaveAsFileName = .SelectedItems(1)
         End If
     End With
 End Function

Private Sub Command23_Click()
Dim strFile As String
     strFile = GetSaveAsFileName("DB_Export")
     If strFile = "" Then
         MsgBox "No filename specified"
     Else
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table1", strPath
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table2", strPath
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table3", strPath
        MsgBox "Export successful'", vbOKOnly, ""
     End If
End Sub

It's not working yet though, It still doesn't create the file path; I get runtime error 2522: 'requires a File Name argument'.

What do I need to change?

[Jaron]

Ok, I've got the following to work:

Function GetSaveAsFileName(Optional InitialName As String) As String
     With Application.FileDialog(2) ' msoFileDialogSaveAs
         .InitialFileName = InitialName
         If .Show Then
             GetSaveAsFileName = .SelectedItems(1)
         End If
     End With
 End Function

Private Sub Command23_Click()
Dim strFileName As String
     strFileName = GetSaveAsFileName("DB_Export")
     If strFileName = "" Then
         MsgBox "Data not exported."
     Else
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table1", strFileName
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table2", strFileName
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "Table3", strFileName
        MsgBox "Data successfully exported to " & strFileName, vbOKOnly, ""
     End If
End Sub

Works good. Hopefully it will on other versions too. I'll keep reading up on split databases. In my case, my users are not networked and manage their own data independently of each other, so they would have to keep their own back end on their own computers, and then I'd just send a new front end if I needed to. Sounds good.