Jay Williams
asked on
Access VBA to reference a network folder for excel import.
My database imports excel files from a network drive (G:\), but these files have been moved there manually from their location at \\Frxfileserv1\...\. We now have permission to the source folder, so we should be able to import directly from that location, but the code doesn't like "\\". How do I reference it correctly?
can you provide the code that isn't working?
ASKER
Here is the code that is not working:
As you can see, I'm opening the file and saving it in an import friendly format before import, and it works fine as long as I move the file manually to the current project path:
Public Sub AX01_MASA_REPORT()
Dim sPath As String, Src1 As String
sPath = \\Frxfileserv1\FTPDOWN\WW_PUBLIC\Download Folder\New Product Programs-MASA\Local\AX01 & "\"
Src1 = sPath & "AX01_MASA_REPORT.XLS"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Src1
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51
xlObj.Quit
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 10, "ProcessorT", Src1, True, ""
xlObj.Quit
DoCmd.SetWarnings True
End Sub
As you can see, I'm opening the file and saving it in an import friendly format before import, and it works fine as long as I move the file manually to the current project path:
Public Sub AX01_MASA_REPORT()
Dim sPath As String, Src1 As String
sPath = CurrentProject.Path & "\"
Src1 = sPath & "AX01_MASA_REPORT.XLS"
Dim xlObj As Object
Set xlObj = CreateObject("excel.application")
xlObj.Workbooks.Open Src1
xlObj.DisplayAlerts = False
xlObj.ActiveWorkbook.SaveAs Src1, FileFormat:=51
xlObj.Quit
DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet acImport, 10, "ProcessorT", Src1, True, ""
xlObj.Quit
DoCmd.SetWarnings True
End Sub
Ok, so what is it that isn't working?
1. does xlObj open properly, with the correct file?
2. you can tell if it opens properly by setting xlObj.Visible = true
3. do you (or your user) have write permissions to the network path?
4. between lines 11 and 12 of the first code segment, insert:
set xlObj = nothing
5. Delete the second line that sets xlObj = Null
6. If you save the file to the network drive manually, in the appropriate format, can you import the data manually, using the External Data process?
7. If that works, try running the TransferSpreadsheet method from the immediate window to import that table, also from the network drive.
1. does xlObj open properly, with the correct file?
2. you can tell if it opens properly by setting xlObj.Visible = true
3. do you (or your user) have write permissions to the network path?
4. between lines 11 and 12 of the first code segment, insert:
set xlObj = nothing
5. Delete the second line that sets xlObj = Null
6. If you save the file to the network drive manually, in the appropriate format, can you import the data manually, using the External Data process?
7. If that works, try running the TransferSpreadsheet method from the immediate window to import that table, also from the network drive.
ASKER
Good questions and suggestions. I rather doubt that we have write permissions, but I'll check and see if the file is opening before the error. I assumed, perhaps incorrectly, that the access was denied before opening; will check it out. If so, I'll write it to the current project path.
Unclear about the handling of xlObj. I get setting it to nothing, but I don't see where it is set to null. What am I missing?
Unclear about the handling of xlObj. I get setting it to nothing, but I don't see where it is set to null. What am I missing?
Jay,
My bad, that should have been
Delete the 2nd line that reads
xlObj.Quit
My bad, that should have been
Delete the 2nd line that reads
xlObj.Quit
ASKER
Gotcha. Working on it now. Thanks.
ASKER
Back to my original question about referencing the folder; this line turns red in the VBE:
sPath = \\Frxfileserv1\FTPDOWN\WW_ PUBLIC\Dow nload Folder\New Product Programs-MASA\Local\AX01 & "\"
sPath = \\Frxfileserv1\FTPDOWN\WW_
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Probably the same way I did. :-)
ASKER
Ok. You answered my question--and more. The file does open, but will not save to the same location, so I'll save it to the current project path, and that should do it. Problem solved, and thank you!
ASKER
Got a the answer to my question and a sloppy code cleanup suggestion to boot. Thanks Dale.
glad I could help.