Greetings,
This site has helped me 'get over the hump' a number of times and I'm hoping that will be the case again.
I've now spent several days on this over the past couple of months. I can't seem to quite get it.
I have a template file where one can set up to a few dozen cell values. Eg - Quote #, Company name, Currency exchange rates, etc.
This file is placed in the the folder that holds a dozen or two Estimate files. A macro is run which opens the Estimate files one by one, updates the values, saves, then closes the file.
This all worked beautifully for many years on our servers. We are now on SharePoint. While we can sync to OneDrive and the macro still works (with a tweak), it is not ideal (each user is a different path).
Here is the core part of the code that I'm trying to adapt. The macro builds a list of filenames (with some conditions) via the Dir command.
I know that I somehow have to modify this so that it can work with the URL path of SharePoint instead of the network path.
Also, the Workbooks.Open will have to handle this path as well.
Aside - I was able to adapt another template macro which generates a list of filenames and path from a SharePoint folder. It involved drive mapping. I've made an attempt to do that here, but no luck so far.
I'm hoping someone can help / point me in the right direction.
Thank you.
This site has helped me 'get over the hump' a number of times and I'm hoping that will be the case again.
I've now spent several days on this over the past couple of months. I can't seem to quite get it.
I have a template file where one can set up to a few dozen cell values. Eg - Quote #, Company name, Currency exchange rates, etc.
This file is placed in the the folder that holds a dozen or two Estimate files. A macro is run which opens the Estimate files one by one, updates the values, saves, then closes the file.
This all worked beautifully for many years on our servers. We are now on SharePoint. While we can sync to OneDrive and the macro still works (with a tweak), it is not ideal (each user is a different path).
Here is the core part of the code that I'm trying to adapt. The macro builds a list of filenames (with some conditions) via the Dir command.
I know that I somehow have to modify this so that it can work with the URL path of SharePoint instead of the network path.
Also, the Workbooks.Open will have to handle this path as well.
Aside - I was able to adapt another template macro which generates a list of filenames and path from a SharePoint folder. It involved drive mapping. I've made an attempt to do that here, but no luck so far.
VBA Code:
strPrj = "*" & Range("PRJ_NO") 'Pick up the Job or Quote number, just to help limit the files this macro will act on. NOTE - This is a portion of the filename.
strPath = ThisWorkbook.Path & "\" 'Pick up the Path and add the backslash.
strFilename = Dir(strPath & strPrj & "*.xls*") 'Restricted to only Excel workbook files.
'If no matches found, then exit the macro.
If strFilename = "" Then
MsgBox "No files found matching: " _
& strPath & strPrj & "*.xls*"
Exit Sub
End If
Application.EnableEvents = False
On Error Resume Next
While strFilename <> ""
Set wbkCurr = Workbooks.Open(strPath & strFilename)
If Not wbkCurr Is Nothing Then
--- Update values...
I'm hoping someone can help / point me in the right direction.
Thank you.