Solved Excel Macro to Save File Named in Specific Cell

Microsoft Office excel 2003
June 23, 2014 at 13:49:52
Specs: Windows 7 Pro 6.1 SP1, Intel Core i5-3470 3.20 GHz; 8GB RAM
I have an Excel 2013 spreadsheet that develops hyperlinks for the user to click on to view certain files. However, I want to be able to give the user the option of saving the source file to a folder on our server. Is there a macro that I could create that would do this? I have researched online extensively and cannot locate anything that does this. I have included a screen capture of the raw data on my spreadsheet. Screen capture is not working for whatever reason so here is my data:

File Path B1 E:\ABC\DOCS\
Client ID B2 4444ABCD
Separator B3 .
File Location B4 https://statements.company.com/ISD....
Preferred File Name B5 Trimark Income Growth Fund A Fund Facts.pdf
Complete Path&File Name B7 E:\ABC\DOCS\4444ABCD.Trimark Income Growth Fund A Fund Facts.pdf

Complete Path&File Name is a formula =B1&B2&B3&B5

Any help would be very appreciated. Thanks in advance.

Steve

message edited by airhockeycanada


See More: Excel Macro to Save File Named in Specific Cell

Report •

✔ Best Answer
June 26, 2014 at 06:42:43
I think I've got it!

I have to give credit to Razor2.3 over in the Web Development forum. I posted my version of your question in that forum and Razor2.3 pointed me to this website where I found the code required to access the external server and download the file.

http://serverfault.com/questions/29...

With some minor customization to have the code pick up the information from your spreadsheet, as opposed to having it hardcoded in the macro, I was able to download the file directly to a folder on my system.

2 items to mention:

1 - The code is set to pull the information from the first Sheet in the workbook:

Sheets(1)

You can either change the number to reference the actual Sheet you are using or you can use the Sheet Name, enclosed in quotes.

Sheets("My Sheet Name")

If you use a number, and then later move the Sheet to different position, the code will not update. It will still reference the Sheet in the position represented by that number.

If you use the Sheet Name, and then later move the Sheet, the code will still reference the Sheet by its Name, so that won't be a problem. However, if you change the name, then the code won't be able to find the Sheet unless you also change the name in the macro.

2 - I noticed that you are building the file path within your spreadsheet (=B1&B2&B3&B5) so that is how I set up the macro. In other words, the code is going to pull the path and filename from B7.

    strHDLocation = Sheets(1).Range("B7")

Just so you know, The building of the path and filename can be done within the code itself, if you don't want to use spreadsheet cells for things like the "."

    strHDLocation = Sheets(1).Range("B1") & _
                    Sheets(1).Range("B2") & _
                    "." & _
                    Sheets(1).Range("B5")


Important Notes:

The code checks to see if a file with the name used in B7 already exists. If it does, it will delete the existing file without a warning. i.e. it will overwrite the existing file.

However, if the file exists and is also Open on the user's system at the same time, the code will fail with a "Permission Denied" error because it can not Delete the file while it is open.

Both of those situations can be dealt with, so let me know if you need me to deal with those issues.

let me know how this works for you:

Sub GetFile()
'Set your settings
    strFileURL = Sheets(1).Range("B4")
    strHDLocation = Sheets(1).Range("B7")

   ' Fetch the file

    Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")

    objXMLHTTP.Open "GET", strFileURL, False
   objXMLHTTP.send

    If objXMLHTTP.Status = 200 Then
      Set objADOStream = CreateObject("ADODB.Stream")
      objADOStream.Open
      objADOStream.Type = 1 'adTypeBinary

      objADOStream.Write objXMLHTTP.ResponseBody
      objADOStream.Position = 0    'Set the stream position to the start

      Set objFSO = CreateObject("Scripting.FileSystemObject")
        If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
      Set objFSO = Nothing

      objADOStream.SaveToFile strHDLocation
      objADOStream.Close
      Set objADOStream = Nothing
    End If

    Set objXMLHTTP = Nothing

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
June 24, 2014 at 05:36:31
That an interesting question. The complexity is that the macro would have to go out, find the file on the external server, and then copy it to the "local" server.

Let me think about this and see what I can do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
June 24, 2014 at 06:00:46
DerbyDad03, that is very kind of you. If you can figure this out that would be awesome. I have done quite a lot of research online trying to find a solution but, no luck. The solutions that are available talk about saving the worksheet itself to different locations with unique names but, not an external file like my situation.

Thanks again.


Report •

#3
June 24, 2014 at 06:09:28
Another thought just came to mind...

While the concept of locally saving a file that resides on an external server sounds intriguing, I assume you are aware that the local content could quickly go stale.

For example, it appears that you are saving a fact sheet for a mutual fund. Since this information is usually updated quarterly, maybe even more often in some cases, you run the risk of not always having the latest information on the local drive.

What about using a macro that places the local save location on the clipboard and then follows the link to the external content? The user would then click the Save button in the PDF window and paste the filepath/filename into the Save field. It's not as "clean" as an automatic save, but it ensures that the local content is always up to date.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
June 24, 2014 at 06:23:55
DerbyDad03, WOW I am amazed that you went to that level but, impressed. For what we need this for, stale info is not an issue. We only need this to save the file to a specific folder on our server for immediate delivery to clients. The pdf shows up in the client portal that the client logs in to.

Thanks for your help on this.


Report •

#5
June 24, 2014 at 09:47:29
DerbyDad03,

Still looking for a solution if possible. Sorry, my last post made it sound like the discussion has ended. If you do have the ability to come up with something I'd appreciate it. No expectations though. You have already been very helpful.

Steve


Report •

#6
June 25, 2014 at 15:51:02
Sorry, but this one is beyond the scope of my talents. I don't know how to have the VBA code act like a browser and go out to the server to download the file directly.

I'm assuming that the server recognizes, and honors, a request coming from a browser and opens the file in the browser window. Then the browser itself allows the user to save a copy of the pdf. I don't enough enough about html coding to "fool" the server into accepting a request from a macro.

I'll keep looking around, but I can't promise anything. Good luck!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#7
June 26, 2014 at 04:17:56
I may have something but I can't work on it until this evening. I'll let you know how it works out.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
June 26, 2014 at 06:09:14
WOW, thanks. I look forward to hearing back on how it worked out.

Report •

#9
June 26, 2014 at 06:42:43
✔ Best Answer
I think I've got it!

I have to give credit to Razor2.3 over in the Web Development forum. I posted my version of your question in that forum and Razor2.3 pointed me to this website where I found the code required to access the external server and download the file.

http://serverfault.com/questions/29...

With some minor customization to have the code pick up the information from your spreadsheet, as opposed to having it hardcoded in the macro, I was able to download the file directly to a folder on my system.

2 items to mention:

1 - The code is set to pull the information from the first Sheet in the workbook:

Sheets(1)

You can either change the number to reference the actual Sheet you are using or you can use the Sheet Name, enclosed in quotes.

Sheets("My Sheet Name")

If you use a number, and then later move the Sheet to different position, the code will not update. It will still reference the Sheet in the position represented by that number.

If you use the Sheet Name, and then later move the Sheet, the code will still reference the Sheet by its Name, so that won't be a problem. However, if you change the name, then the code won't be able to find the Sheet unless you also change the name in the macro.

2 - I noticed that you are building the file path within your spreadsheet (=B1&B2&B3&B5) so that is how I set up the macro. In other words, the code is going to pull the path and filename from B7.

    strHDLocation = Sheets(1).Range("B7")

Just so you know, The building of the path and filename can be done within the code itself, if you don't want to use spreadsheet cells for things like the "."

    strHDLocation = Sheets(1).Range("B1") & _
                    Sheets(1).Range("B2") & _
                    "." & _
                    Sheets(1).Range("B5")


Important Notes:

The code checks to see if a file with the name used in B7 already exists. If it does, it will delete the existing file without a warning. i.e. it will overwrite the existing file.

However, if the file exists and is also Open on the user's system at the same time, the code will fail with a "Permission Denied" error because it can not Delete the file while it is open.

Both of those situations can be dealt with, so let me know if you need me to deal with those issues.

let me know how this works for you:

Sub GetFile()
'Set your settings
    strFileURL = Sheets(1).Range("B4")
    strHDLocation = Sheets(1).Range("B7")

   ' Fetch the file

    Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")

    objXMLHTTP.Open "GET", strFileURL, False
   objXMLHTTP.send

    If objXMLHTTP.Status = 200 Then
      Set objADOStream = CreateObject("ADODB.Stream")
      objADOStream.Open
      objADOStream.Type = 1 'adTypeBinary

      objADOStream.Write objXMLHTTP.ResponseBody
      objADOStream.Position = 0    'Set the stream position to the start

      Set objFSO = CreateObject("Scripting.FileSystemObject")
        If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
      Set objFSO = Nothing

      objADOStream.SaveToFile strHDLocation
      objADOStream.Close
      Set objADOStream = Nothing
    End If

    Set objXMLHTTP = Nothing

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
June 26, 2014 at 11:53:51
WOW, amazing. I will give this a try and let you know. Again, thanks for this.

Report •

#11
June 26, 2014 at 14:16:39
IT WORKS! That is amazing. There is one thing I have to ask that I think you might know. It involves looping and doing a data check. There are up to 15 of these files that can be saved based on what the user inputs. Each item is on a different row so for example for the first item the cells are B4 and B7 but for the next it's B8 and B11 and so on. What I want to do is have the user be able to click that "save files" button and have the macro save the files of only the items that have been entered. I did build the macro to save all 15 but, as soon as it tries to for the first item not present it give a run time error. I just think it would be cleaner without the run time error.

If you know how to do this easily, great. If not, please don't go to any trouble as you have already gone above and beyond.

Thanks!


Report •

#12
June 26, 2014 at 14:52:33
Since I don't know how you "built the macro to save all 15“ it's hard for me to offer a specific suggestion. Without seeing your code, the best I can suggest is to use an If statement to check for the presence of data in whatever cell is causing the error.

e.g.

      If Sheets(1).Range("B7") = "" Then
           'Exit the loop 

If you want to post your code, I can probably be more specific. Please use the pre tags before posting. Click on the following line if you are not familiar with using the pre tags

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
June 27, 2014 at 07:10:16
I'm obviously not great with VB. I basically copied the macro that does one save 15 times and changed the coordinates for the save file name and source file URL for each. I have shown you an example below of the code for doing 2 saves. If the second coordinates have no data it returns a run time error. Ideally, when successful this should have a dialog box pop up with the message "The files have been saved successfully!" Again, thanks for helping out a newbie. I am actually learning quite a bit with this as well.

Steve

Sub SavetoFDNAllFunds()
'
' SavetoFDNAllFunds Macro
' Saves all Fund Fact sheets (max 15) for the client.
'
'Set your settings
    strFileURL = Sheets(1).Range("R8")
    strHDLocation = Sheets(1).Range("S8")

   ' Fetch the file

    Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")

    objXMLHTTP.Open "GET", strFileURL, False
   objXMLHTTP.send

    If objXMLHTTP.Status = 200 Then
      Set objADOStream = CreateObject("ADODB.Stream")
      objADOStream.Open
      objADOStream.Type = 1 'adTypeBinary

      objADOStream.Write objXMLHTTP.ResponseBody
      objADOStream.Position = 0    'Set the stream position to the start

      Set objFSO = CreateObject("Scripting.FileSystemObject")
        If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
      Set objFSO = Nothing

      objADOStream.SaveToFile strHDLocation
      objADOStream.Close
      Set objADOStream = Nothing
    End If

    Set objXMLHTTP = Nothing
'Set your settings
    strFileURL = Sheets(1).Range("R9")
    strHDLocation = Sheets(1).Range("S9")

   ' Fetch the file

    Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")

    objXMLHTTP.Open "GET", strFileURL, False
   objXMLHTTP.send

    If objXMLHTTP.Status = 200 Then
      Set objADOStream = CreateObject("ADODB.Stream")
      objADOStream.Open
      objADOStream.Type = 1 'adTypeBinary

      objADOStream.Write objXMLHTTP.ResponseBody
      objADOStream.Position = 0    'Set the stream position to the start

      Set objFSO = CreateObject("Scripting.FileSystemObject")
        If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
      Set objFSO = Nothing

      objADOStream.SaveToFile strHDLocation
      objADOStream.Close
      Set objADOStream = Nothing
    End If

    Set objXMLHTTP = Nothing

End Sub



Report •

#14
June 27, 2014 at 11:18:21
Before I address your latest question, I want to point you towards a How-To I wrote which may help you increase your VBA skills. I wrote this many years ago while I was learning VBA because I found the techniques listed to be of great value.

http://www.computing.net/howtos/sho...


OK, as for your current project...

Rather than replicating the download section 15 times, the more efficient thing to do would be to loop through the code with a For-Next loop. By only using one core section of code, it makes it much easier to edit should you need to change it in the future.

First, let's look at a very simple For-Next loop:

A basic For-Next loop will increment by one each time through, but a Step argument can also be added. For example, this code will loop 5 times, presenting a MsgBox showing you the value of the variable used for the loop. i.e. 1, 2, 3, 4, 5


Sub IncrementByOne()
  For myVar = 1 To 5
     MsgBox myVar
  Next
End Sub

This code will also count from 1 to 5, but it will count by 2's, presenting a MsgBox showing 1, 3, 5.

Sub IncrementBytwo()
  For myVar = 1 To 5 Step 2
      MsgBox myVar
  Next
End Sub

We can also use the loop variable to build the range reference. This code will put value of the loop variable in A1:A5

 
Sub PopulateByOne()
  For rwNum = 1 To 5
       Sheets(1).Range("A" & rwNum) = rwNum
  Next
End Sub

Ok, since your latest code seems be pulling data from consecutive rows (R8, R9, etc.) [which I believe is a change from your previous version] we can use a For-Next loop without a Step argument:

Sub SavetoFDNAllFunds()
'
' SavetoFDNAllFunds Macro
' Saves all Fund Fact sheets (max 15) for the client.
'
'Loop through Rows 8 - 22
    For rwNum = 8 To 22
'Set your settings
       strFileURL = Sheets(1).Range("R" & rwNum)
       strHDLocation = Sheets(1).Range("S" & rwNum)

'*** The rest of the download code goes here ***

'Return to top of loop
    Next

End Sub

Next, if you want to eliminate the error that occurs when you don’t have 15 files to download, you can perform a check to see if a cell is empty and the exit the Loop.

Sub SavetoFDNAllFunds()
'
' SavetoFDNAllFunds Macro
' Saves all Fund Fact sheets (max 15) for the client.
'
'Loop through Rows 8 - 22
    For rwNum = 8 To 22
'Check for empty URL cell, Exit Loop if empty
     If Sheets(1).Range("R" & rwNum) = "" then Exit For
'Set your settings
       strFileURL = Sheets(1).Range("R" & rwNum)
       strHDLocation = Sheets(1).Range("S" & rwNum)

'*** The rest of the download code goes here ***

'Return to top of loop
     Next

End Sub

Last, but not least if you want a MsgBox announcing the completion of the downloads, you could do something like this:

Sub SavetoFDNAllFunds()
'
' SavetoFDNAllFunds Macro
' Saves all Fund Fact sheets (max 15) for the client.
'
'Loop through Rows 8 - 22
    For rwNum = 8 To 22
'Check for empty URL cell, Exit Loop if empty
     If Sheets(1).Range("R" & rwNum) = "" then Exit For
'Set your settings
       strFileURL = Sheets(1).Range("R" & rwNum)
       strHDLocation = Sheets(1).Range("S" & rwNum)

'*** The rest of the download code goes here ***

'Return to top of loop
     Next

'Announce end of download

   MsgBox "All Requested Files have Been Downloaded"

End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#15
June 27, 2014 at 11:28:44
BTW...my last suggestion about using the MsgBox is going to present the MsgBox even if no files were downloaded. You could fix that by adding a variable that counts how many files were downloaded.

For example, if you put this line before the Next statement, it will increment each time the download code completes:

numFiles = numFiles + 1

Then, in the MsgBox instruction, you could use:

MsgBox numFiles & " Files have Been Downloaded"

One last thing:

I did not do very much testing of the For-Next loop suggested earlier. I know a For-Next loop will work, but I did not test my posted syntax because I didn't take the time to set up a spreadsheet with links, paths, filename, etc. Just let me know if anything throws up an error and I'll see what's wrong.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#16
June 27, 2014 at 12:23:24
Again, amazing! Below is the macro script I employed based on your instruction and it works well. The only thing I noticed is that if someone clicks the save button to initiate the macro and there is NO data, the dialog box pops up saying " files have been saved." In other words, it leaves out the "0". If this is easily corrected, great. Otherwise, I am good to go and thank you VERY VERY MUCH for all the work you put into this. I appreciate the tutorial link and will definitely delve into it to improve my VBA skills.

Steve

Sub SavetoFDNAllFunds()
'
' SavetoFDNAllFunds Macro
' Saves all Fund Fact sheets (max 15) for the client.
'
'Loop through Rows 8 - 22
    For rwNum = 8 To 22
'Check for empty URL cell, Exit Loop if empty
     If Sheets(1).Range("R" & rwNum) = "" then Exit For
'Set your settings
       strFileURL = Sheets(1).Range("R" & rwNum)
       strHDLocation = Sheets(1).Range("S" & rwNum)

   ' Fetch the file

    Set objXMLHTTP = CreateObject("MSXML2.XMLHTTP")

    objXMLHTTP.Open "GET", strFileURL, False
   objXMLHTTP.send

    If objXMLHTTP.Status = 200 Then
      Set objADOStream = CreateObject("ADODB.Stream")
      objADOStream.Open
      objADOStream.Type = 1 'adTypeBinary

      objADOStream.Write objXMLHTTP.ResponseBody
      objADOStream.Position = 0    'Set the stream position to the start

      Set objFSO = CreateObject("Scripting.FileSystemObject")
        If objFSO.Fileexists(strHDLocation) Then objFSO.DeleteFile strHDLocation
      Set objFSO = Nothing

      objADOStream.SaveToFile strHDLocation
      objADOStream.Close
      Set objADOStream = Nothing
    End If

    Set objXMLHTTP = Nothing

'Return to top of loop
    numFiles = numFiles + 1
     Next

'Announce end of download

   MsgBox numFiles & " files have been saved."

End Sub


Report •

#17
June 27, 2014 at 12:41:44
Although I'm not sure why the user would click a Save button when there is nothing to save, this should resolve the issue:

Replace the single MsgBox instruction with this:


'Announce end of download

   If numFiles = 0 Then

     MsgBox "There were no files in the download list."
         Exit Sub

   Else

      MsgBox numFiles & " files have been saved."
   
   End If

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#18
July 2, 2014 at 07:04:23
Perfect. Thanks again for all of your help. You have been over-the-top helpful and I really do appreciate it. Again, thanks for the tutorial link. I will most definitely be checking that out.

Take care!

Steve


Report •

Ask Question