DestinationFile (1 Viewer)

[prasadgov]

Hi,

I am trying to save my file to a location in another server

My SourceFile is in CurrentProject.Path
My DestinationFile is something like " \\xx.xxx.xx.xxx\backup\LReports\ & "\" & "reportName_" & sDateTimeStamp & ".xlsm"

When I try FileCopy SourceFile, DestinationFile,
I get an error in the Destination path.
Can I copy to a backup server (which is a valid path when I tried manually), and if so, is the code correct?

TIA

 

[moke123]

My DestinationFile is something like " \\xx.xxx.xx.xxx\backup\LReports\ & "\" & "reportName_" & sDateTimeStamp & ".xlsm"

see anything wrong here?

LReports\ & "\" & "reportName_"

 

[theDBguy]

Also, what is in sDateTimeStamp? What exactly was the error message?

 

[prasadgov]

see anything wrong here?

LReports\ & "\" & "reportName_"

Also, what is in sDateTimeStamp? What exactly was the error message?

My actual code is

Public Function ExportTblToExcel()

Dim FullFilePath As String
Dim sDateTimeStamp As String
Dim sFolderLocation As String
Dim SourceFile, DestinationFile As String


sDateTimeStamp = CStr(Year(Now())) & _
                 Pad(CStr(Month(Now())), 2) & _
                 Pad(CStr(Day(Now())), 2) & _
                 Pad(CStr(Hour(Now())), 2) & _
                 Pad(CStr(Minute(Now())), 2) & _
                 Pad(CStr(Second(Now())), 2)
 
sFolderLocation = CStr(Year(Now())) & _
                 Pad(CStr(Month(Now())), 2)

SourceFile = CurrentProject.Path & "\TemplatePipe\" & "Report_Template.xlsm"
DestinationFile = "\\xx.xxx.xx.xx\backup\LReports" & "\Output\" & sFolderLocation & "\" & "Report_" & sDateTimeStamp & ".xlsm"

FileCopy SourceFile, DestinationFile

..............
.........

End Function

 

[theDBguy]

My actual code is

What was the actual error message? What was the code behind Pad()?

 

[prasadgov]

What was the actual error message? What was the code behind Pad()?

ahh...I missed a folder in between
It's now outputting.
Still not totally recovered from the weekend ....

 

[theDBguy]

ahh...I missed a folder in between
It's now outputting.
Still not totally recovered from the weekend ....

Glad to hear you got it sorted out. Good luck with your project.

 

[The_Doc_Man]

" \\xx.xxx.xx.xxx\backup\LReports\ & "\" & "reportName_" & sDateTimeStamp & ".xlsm"

In this specific case you had both a quoting error and (potentially) an empty path, both centered around the first two ampersands. Odds are that one or both of those caused you problems.

The general suggestion is to always build the string completely first so that you can print out or examine the full path you are using.

As you know, since you say it is working now, Access can put a file anywhere that you could manually put one using Windows File Explorer and a cut/paste operation. If you have the required permissions, it is a perfectly legal operation.