Export Help (1 Viewer)

[Acropolis]

Hi

I have the following setup to export some queries to Excel files. It on a loop and exports each one in turn.

This has been working fine for month, then all of a sudden this morning it stops working, saying it's not a valid path, even though if I debug.print the path copy and paste it, the location opens, any ideas?

'Set year path to save file
Path1 = "S:\Obox Techincal Services\Daily Checks\" & Format(DateToCheck, "yyyy") & "\" & Format(DateToCheck, "mmmm")

If Dir(Path1, vbDirectory) = "" Then
    sPath = Path1
    CreateDirectory (sPath)
End If

'Daily check files
Set rsExports = db.OpenRecordset("SELECT * FROM tblReports WHERE OboxChecks = 1 AND isLive = 1 ORDER BY ID ASC")

rsExports.MoveLast
rsExports.MoveFirst

Do Until rsExports.EOF
    Filename = Path1 & "\" & Format(DateToCheck, "dd-mm-yyyy") & rsExports("SaveName")
'    Filename = Filename & rsExports("Fileformat")
'    DoCmd.TransferSpreadsheet acExport, , rsExports("RptName"), Filename '& ".xlsx"
    DoCmd.OutputTo acOutputQuery, rsExports("RptName"), "ExcelWorkbook(*.xlsx)", Filename & ".xlsx", False, "", , acExportQualityPrint
    Filename = Filename & rsExports("Fileformat")
    Qry = rsExports("SheetName")
    Conditional = 0
    Call FormatExport(Filename, Qry, Conditional)
    rsExports.MoveNext
Loop

 

[arnelgp]

are you using mapped drive? does the path has static ip address?

 

[Acropolis]

Yes it is a mapped drive.

Not sure about the static IP as they are network shares controlled by the IT dept.

If i enter the path into windows explorer it opens absolutely fine, but after setting the path above, if i get the code to open the folder from there it doesn't open it, goes to my documents instead.

Have just changed the path to the server name and location as opposed to the "S:\" and it worked fine, so not sure what is going on there.

Time to bug IT dept me thinks.

 

[Grumm]

Do you still see the S:\ Drive on your machine ? If the server name works, then i guess the drive letter changed or something.

Maybe it is a good idea to modify your code and use the server alias instead. That way when they replace or change the server your code still works.