By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,149 Members | 1,831 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,149 IT Pros & Developers. It's quick & easy.

MS Access Command Button To Download File

P: 27
Hi All,
I got really good help here last time so i thought i'd try again !.
I'd like to have a command button on my form which does the following -
1. Downloads a file from a url
2. Saves the file in a particular folder
3. Renames the file as Suppliers.xlsx
4. Imports the data into an existing table (overwriting current records)

It sounds a far stretch to me but maybe you guys can tell me if it's possible and how to do it using VBA ?

Thank you
3 Weeks Ago #1
Share this Question
Share on Google+
18 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,482
Pol53,

This might be possible, but I think your challenge is going to be your first point, as downloading from a URL might be tricky.

If the URL is the file name which is housed on that site, then, based upon your web browser settings, it might download automatically to your default downloads folder. Then, once it's there, you can do what you want with it.

If that URL does not include the file name itself, then, personally, I wouldn't know how to begin approaching this problem.

Is there a reason you can't simply download the file manually and then work with it from there? I understand this could be one of those repetitive actions, so automation may be desired.

Not sure if this hepps, but it might hepp us explore more of the nature of this question.
3 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,761
Hi Pol53.

We're generally happy to help push you along, but we help YOU to do it. We don't do it for you.

You need to start work on this and let us know where and exactly how you struggle.

We want to help. It may be interesting. However, we aren't even allowed to just take requests for work and do things for you.
3 Weeks Ago #3

P: 27
Thanks for the replies guys. It's no problem downloading the file manually and renaming it. From there i can just set up a button to import the records into an existing table. I thought that i might possibly be able to also automate the downloading part. I was thinking of creating a macro or something to handle the whole process but i don't think there's an action available for downloading files ?
3 Weeks Ago #4

twinnyfo
Expert Mod 2.5K+
P: 3,482
If I understand things properly (and I'm not 100% sure that I do), being able to automate a web page in a browser would require more intimate knowledge of the web page itself, being able to execute commands associated with particular objects/links on that site.

The rest has probably been covered in detail elsewhere on this forum.

Thanks for the clarification.

As you work through this project, feel free to post additional problem areas.
3 Weeks Ago #5

Rabbit
Expert Mod 10K+
P: 12,427

twinnyfo
Expert Mod 2.5K+
P: 3,482
Rabbit,

Wonderful article! Not sure I have a direct need for this, but if I did, I could see it as very useful, particularly if a website kept updating an online file.

Where is the "Thumbs up!" button?
3 Weeks Ago #7

P: 27
Thanks a lot for the suggestion Rabbit....unfortunately it sailed about a mile over my head. I'm not very knowledgeable when it comes to VBA
3 Weeks Ago #8

P: 27
I found the code below and gave it a try but am getting a "Variable not defined" error. The line Set oStrm is highlighted

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Command7_Click()
  5. Dim myURL As String
  6. myURL = "www.mywebsite/file.xlsx"
  7.  
  8. Dim HttpReq As Object
  9. Set HttpReq = CreateObject("Microsoft.XMLHTTP")
  10. HttpReq.Open "GET", myURL, False, "username", "password"
  11. HttpReq.send
  12.  
  13. myURL = HttpReq.responseBody
  14. If HttpReq.Status = 200 Then
  15.     Set oStrm = CreateObject("ADODB.Stream")
  16.     oStrm.Open
  17.     oStrm.Type = 1
  18.     oStrm.Write HttpReq.responseBody
  19.     oStrm.SaveToFile ThisWorkbook.Path & "\" & "suppliers.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
  20.     oStrm.Close
  21. End If
  22. End Sub
  23.  
3 Weeks Ago #9

twinnyfo
Expert Mod 2.5K+
P: 3,482
Pol53,

Because you say you are new to VBA, we will allow some leeway. However, rather than searching for another bit of code, did you, by chance, try Rabbit's code?

If you had, you could have included his code in your form, and added the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command7_Click()
  2.  
  3.     If InternetGetFile(sURLFileName:="www.mywebsite/file.xlsx", _
  4.                        sSaveToFile:=ThisWorkbook.Path & "\" & "suppliers.xlsx", _
  5.                        bOverwriteExisting:=True) Then
  6.         MsgBox "File Saved Successfully!"
  7.     Else
  8.         MsgBox "File Download Failure!"
  9.     End If
  10.  
  11. End Sub
That "should have" produced better results. The code you supplied will break (my guess) at several points. Again, this is because you don't fully understand VBA, declaring variables and objects, etc.

Please don't take that as a personal slam, but as one of guidance. In order to best use VBA, you need to understand the basics of VBA programming and how to create workable procedures. Once you understand those basics, you can 1) better troubleshoot those procedures that don't work properly and 2) build on those basic foundations to have more expansive and more robust projects in VBA.

You can ask Rabbit (and others on this forum). When I started here, I was pretty clueless. Most of us are willing to answer sincere questions about VBA--especially if folks don't understand what we are proposing as solutions. Our goal is to make you independent and creative--we want you to learn your craft and be able to experiment with VBA to build bigger and better things.

Hope this hepps!
3 Weeks Ago #10

ADezii
Expert 5K+
P: 8,692
Sorry for jumping in late, but here is my personal preference for Downloading a File from an Internet URL. It consists of only a couple of steps and the complexity is minimal and shielded from the User. I created a USER DEFINED SECTION for you where you only need to specify 2 Values, the URL and the Path that you wish to store the File in. In his Demo the Filename is extracted from the URL, but you can simply name it Suppliers.xlsx and append it to the Path Constant. This Demo is actually functional and will download a *.Zip File from MSDN.com into a Test Folder.
  1. Copy-N-Paste the following API Declaration into a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _
    2.        Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, _
    3.        ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
  2. Base Code Definition:
    Expand|Select|Wrap|Line Numbers
    1. Dim lngRetVal As Long
    2. '****************************** USER DEFINED SECTION ******************************
    3. Const conURL = "https://github.com/microsoftgraph/powershell-intune-samples/" & _
    4.                "releases/download/158068/Intune_Graph_Samples.zip"
    5. Const conPATH = "C:\Test\"
    6. '**********************************************************************************
    7.  
    8. lngRetVal = URLDownloadToFile(0, conURL, conPATH & Mid$(conURL, InStrRev(conURL, "/") + 1), 0, 0)
    9.  
    10. If lngRetVal = 0 Then
    11.   MsgBox "Download was a success.", vbInformation, "Download Complete"
    12. Else
    13.   MsgBox "The download could not be accomplished!", vbExclamation, "Download Failure"
    14. End If
3 Weeks Ago #11

P: 27
twinnyfo...thanks again and yes i do need some basic understanding of VBA which is why I've signed up to an online training course for beginners. I'm going to get started this weekend so hopefully my knowledge should improve soon. I didn't use Rabbit's code because it frightened the life out of me and i didn't know what to do with it. However i'm going to give both versions a try.

ADezii…...thanks for your suggestion. I created a module and just called it Module1 is this ok ?
What do i do with the second part of the code ?......does it go in the OnClick event of the command button ?

Thanks again guys
3 Weeks Ago #12

ADezii
Expert 5K+
P: 8,692
  1. Realizing that 'A Picture is worth a thousand words', I am uploading you a Demo that will help you with at least the first part of your problem. It is self contained, fully operational, and even creates the Test Folder (C:\Test) for you should it not exist. All you need to do is to change the two Values in the USER DEFINED SECTION, keeping in mind of course that the URL must be valid or an Error will occur. I am making an assumption that a Username/Password is not required since it was never mentioned. Good Luck and if you need further assistance, we are here.
  2. It appears as though the File to be download is an Excel File (*.xlsx) and you want it to be renamed to Suppliers.xlsx. Should this be the case, then you will need a minor Code adjustment:
    Expand|Select|Wrap|Line Numbers
    1. lngRetVal = URLDownloadToFile(0, conURL, conPATH & "\" & Suppliers.xlsx, 0, 0)
Attached Files
File Type: zip Download File.zip (26.4 KB, 8 views)
3 Weeks Ago #13

P: 27
ADezii that worked perfectly and i understand about replacing those two values for my url and folder path. One final question.....if i download the file and then in a month's time download it a second time, will the second file just overwrite the first ?
3 Weeks Ago #14

ADezii
Expert 5K+
P: 8,692
Actually, I haven't tested that. I assume that it would overwrite it, but again, I can't say with 100% certainty. This is definitely something that you can easily test. It if does NOT overwrite the File, that you can DELETE it prior to performing the actual Download, as in:
Expand|Select|Wrap|Line Numbers
  1. 'If Suppliers.xlsx exists, then DELETE it
  2. If Dir$(conPATH & "\Suppliers.xlsx", vbNormal) <> "" Then
  3.   Kill conPATH & "\Suppliers.xlsx"
  4. End If
  5.  
  6. 'Now OK to Download
3 Weeks Ago #15

NeoPa
Expert Mod 15k+
P: 31,761
Pol53:
I didn't use Rabbit's code because it frightened the life out of me and i didn't know what to do with it. However i'm going to give both versions a try.
That's fair. Please though, consider how you behave towards experts who expend their time & energy trying to help you. It's very understandable that you may not be able to make best use of some of the suggestions, but that shouldn't mean that they get no response. I'm sure the last thing you'd want to do is to cause someone offering their help to be left disheartened.

Otherwise, all good. I can see you're getting the help you need. You're looking to develop your skills further - which is a responsible approach we all applaud. I would strongly recommend, for most members but particularly for someone who is clearly interested to progress, that you do your best to understand the help given. This takes you further. Perhaps we'll have you answering questions here for others in time. Don't think it can't happen. We have many good examples here of where it already has ;-)
3 Weeks Ago #16

P: 27
Hi NeoPa…..thanks for the advice it's much appreciated. However, if you look back through the thread i did actually thank Rabbit for his suggestion. I know i didn't use the code in the end but that's because i didn't understand it and not because i didn't value the time Rabbit took to help me. I would certainly like to answer questions here someday but i'm probably not quite there at the moment :)

ADezii….thanks again for the help. I'll test it out and see how it goes.
3 Weeks Ago #17

Rabbit
Expert Mod 10K+
P: 12,427
No worries, the code in the link is pretty much the same method Adezii uses
3 Weeks Ago #18

NeoPa
Expert Mod 15k+
P: 31,761
So you did. So you did.

My bad. Forget that part of what I said then. The rest is all good anyway so keep on keeping on. We all love to watch your progress :-)
3 Weeks Ago #19

Post your reply

Sign in to post your reply or Sign up for a free account.