I have received from a third party thgough a client a database with a series of queries and macros to export civil survey data with attributes into excel once it is populated using an addon for Civil3D. The problem is the module that is ran to finish up and export the data has an absolute export path and I can't seem to figure out how to update it correctly. Without going too far into detail one table is populated with survey data, a new table is cleared and joined to this initial table, the data is then exported along with a series of similar tables. This is all accomplished by using a few simple macros to have all tables be loaded at once then exported. The final export is using the module in question. I am a newer Access and VBA user so editing an existing database and module is quite the challenge. Any help would be greatly appreciated.
Here is the module:
Option Compare Database
Code:
Function exportFile()
On Error GoTo Macro1_Err
Dim rs As Object
Set rs = CurrentDb.OpenRecordset("select alignment.name from alignment")
FileName = rs.Fields(0).Value
rs.Close
DoCmd.TransferSpreadsheet acExport, 9, "select - centerline pods load", "R:\Active\Filepathfromthirdparty" & FileName & ".xls", False, ""
MsgBox ("R:\Active\Filepathfromthirdparty" & FileName & ".xls is done")
Macro1_Exit:
Exit Function
Macro1_Err:
MsgBox Error$
Resume Macro1_Exit
End Function
Running it resulted in "... not a valid path. Make sure the path name is spelled correctly and that you are connected to the server on which the file resides," and changing the filepath to somewhere local (C:\Users\alexj\Documents\ for example) and running it resulted in "Failure creating file."