Does ChDir work with a system location?

[William86]

I receive spreadsheets from various sources (via email). Once received and opened I use VBA to add a timestamp to the xls file name and then want to save them all to the same folder location on the server using a network address ... for example "X:\central\excel". I run this from a workstation, and instead of saving to the network folder it keeps saving the files to C:\documents\ on the local machine. My VBA line is simply ChDir "X:\central\excel" ... what am I missing please?

 

[William86]

Sorry, I forgot to mention that I am using Excel 365 on Win8.1

 

[arnelgp]

You can copy it directly without changing folder.

Filecopy sourceFile, targetFile

 

[William86]

OK, but I DO want to change the folder from the current folder on the local machine to a network folder on the Server? at the moment I change the directory for the save folder to what I want on the Server and then do "SaveAs" which works fine other than it is in C:\documents instead of the network folder?

 

[arnelgp]

Maybe this link can help you https://social.technet.microsoft.co...rectory-to-a-network-drive-in-vba?forum=excel

 

[William86]

Thanks, I will try it!

 

[Minty]

Access doesn't know or care what your current folder is, you have to tell VBA to use a specific folder, as per Arne's suggestion.

Also I would recommend that you use a UNC path rather than a specific share directory. e.g.

\\YourFileShare\MainFolder

will always work if they have network access, x:\MainFolder will only work if that drive is mapped on their PC.

So your code should be something like

Filecopy c:\LocalSaveLocation\Myfile.xls  \\Server\CentralStore\MyFile.xls

If you store and lookup the central location in a table, if you ever move it you only have to update the value in the one field , not recode everywhere you use it.

 

[William86]

OK, yes, I understand. In my case the X:\ drive on the Server is mapped on the local machine, but I will try \\Server\ instead.
thanks for the advice!