I want to have a file exported from a query to a folder. The problem is that this database is used by multiple people and that folder can be located in different drives. How can I use the network name to export to this folder? Below is the code that I am using to do my export. It errors out when I include the network drive as part of the file name.
Option Compare Database
Private Sub Command2_Click()
Dim MyFolder As String
Dim MyFile As String
Dim MyFolder2 As String
Dim FilePath As String
Dim FileContent As String
Dim TextFile As Integer
Dim MyFile2 As String
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Show
.InitialFileName = .SelectedItems.Item(1)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from NewBadRecips"
MyFolder2 = .InitialFileName
MyFile = Dir(MyFolder2)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from NewBadRecips"
DoCmd.TransferText acImportDelim, "Import Specification", "NewBadRecips", MyFile, True
DoCmd.TransferSpreadsheet transferType:=acExport, TableName:="NewBadRecipsQuery", _
filename:="\\NAEAST.AD.JPMORGANCHASE.COM\AMERTSS$\TSSTSERV\FLSHARE05\DATA18\LIST MAINTENANCE\FRAUD- RETAIL\CLR Templates\NewBadRecipsQuery.xlsx", hasfieldnames:=True
Name "K:\LIST MAINTENANCE\FRAUD- RETAIL\CLR Templates\NewBadRecipsQuery.xlsx" As "K:\LIST MAINTENANCE\FRAUD- RETAIL\CLR Templates" & MyFile
DoCmd.SetWarnings False
MsgBox "Process Complete"
End With
End Sub
Option Compare Database
Private Sub Command2_Click()
Dim MyFolder As String
Dim MyFile As String
Dim MyFolder2 As String
Dim FilePath As String
Dim FileContent As String
Dim TextFile As Integer
Dim MyFile2 As String
Dim fso As Object
Set fso = CreateObject("Scripting.FileSystemObject")
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Show
.InitialFileName = .SelectedItems.Item(1)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from NewBadRecips"
MyFolder2 = .InitialFileName
MyFile = Dir(MyFolder2)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from NewBadRecips"
DoCmd.TransferText acImportDelim, "Import Specification", "NewBadRecips", MyFile, True
DoCmd.TransferSpreadsheet transferType:=acExport, TableName:="NewBadRecipsQuery", _
filename:="\\NAEAST.AD.JPMORGANCHASE.COM\AMERTSS$\TSSTSERV\FLSHARE05\DATA18\LIST MAINTENANCE\FRAUD- RETAIL\CLR Templates\NewBadRecipsQuery.xlsx", hasfieldnames:=True
Name "K:\LIST MAINTENANCE\FRAUD- RETAIL\CLR Templates\NewBadRecipsQuery.xlsx" As "K:\LIST MAINTENANCE\FRAUD- RETAIL\CLR Templates" & MyFile
DoCmd.SetWarnings False
MsgBox "Process Complete"
End With
End Sub
Last edited: