Problems saving workbook using cell values in path and file name

[jconkl02]

Hi guys,

I'm trying to modify some script that I found on the forum. As it is written it will go out to Sharepoint grab a specific file and save it to a specific path on my hard drive. I am trying to modify it to use a couple of values from the worksheet "MACRO" so that the rest of my team can use the workbook and save the SharePoint file to their hard drive without having to modify the vba.

Here is the original script:

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
                                           "URLDownloadToFileA" ( _
                                           ByVal pCaller As Long, ByVal szURL As String, _
                                           ByVal szFileName As String, _
                                           ByVal dwReserved As Long, _
                                           ByVal lpfnCB As Long) As Long




Sub S_SharepointExport()
Dim i As Integer


    Const strUrl As String = "https://sites.xxxxxxxx.com/network1/servicedesk/NCC/Shared Documents/Notification Quality Control/01 Quality Notification Review/09_2017 Quality Review - September.xlsx"
    Dim strSavePath As String
    Dim returnValue As Long


    
    strSavePath = "C:\Users\MYLOGINID\Documents\NCC MACRO\NCC Quality Control\09_2017 Quality Review - September.xlsx"
    returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)
    
End Sub

What I want to do is make the file name in both strURL and strSavePath be populated from the value in cell K3 in worksheet "MACRO". Also, I want the MYLOGINID section of strSavePath to be populated from the value in cell L2 of worksheet "MACRO".

As always, your help is greatly appreciated.

Jason

 

[jmacleary]

Hi there

Modified code that should do the trick (or get you on the right lines anyway):

Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" Alias _
                                           "URLDownloadToFileA" ( _
                                           ByVal pCaller As Long, ByVal szURL As String, _
                                           ByVal szFileName As String, _
                                           ByVal dwReserved As Long, _
                                           ByVal lpfnCB As Long) As Long




Sub S_SharepointExport()
Dim i As Integer


    Dim strUrl As String
    Dim strSavePath As String
    Dim returnValue As Long

    strUrl = "https://sites.xxxxxxxx.com/network1/servicedesk/NCC/Shared Documents/Notification Quality Control/01 Quality Notification Review/" + Worksheets("MACRO").Range("K3").Value
    strSavePath = "C:\Users\" + Worksheets("MACRO").Range("L2").Value + "\Documents\NCC MACRO\NCC Quality Control\" + Worksheets("MACRO").Range("K3").Value
    
    MsgBox strUrl
    MsgBox strSavePath
    returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)
    
End Sub