pdvsa
asked on
Export code - file path based on either at Home or Work
Experts, how could I modify the below code to choose the file path being exported to? I continually have to change the code based on if I am at home or at work.
thank you
thank you
Private Sub cmdExport_Click()
Dim shtName As String
shtName = "Export-" & Format(Date, "yyyymmdd")
'Work:
DoCmd.TransferSpreadsheet acExport, , "qryExport_FC_Archive_4Pivot", "C:\Users\johnsoat\Desktop\ExportArch4Pivot.xls", True, shtName
'home:
DoCmd.TransferSpreadsheet acExport, , "qryExport_FC_Archive_4Pivot", "C:\Users\pdvsa\Desktop\ExportArch4Pivot.xls", True, shtName
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try using environ("userProfile) which will give you "C:\Users\pdvsa" if you are at HOME or "C:\Users\johnsoat" if you are at WORK
Dim shtName As String
shtName = "Export-" & Format(Date, "yyyymmdd")
DoCmd.TransferSpreadsheet acExport, , "qryExport_FC_Archive_4Pivot", environ("userProfile) & "\Desktop\ExportArch4Pivot.xls", True, shtName
Don't hardcode paths. It is too tough to find them all if they need to be changed. It is better to create a table to hold certain types of settings. The table can include a HomePath and WorkPath. The login form should have an option group for Home or Work and the exports check the option and then use the correct path. Have the option group either default to the most common usage or make it required and have the user choose each time.
ASKER
thank you.
pls try
Open in new window
Regards