MS Access Command Button To Download File

[Pol53]

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

[twinnyfo]

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.

[NeoPa]

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.

[Pol53]

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 ?

[twinnyfo]

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.

[Rabbit]

https://bytes.com/topic/access/insig...rnet-using-vba

[twinnyfo]

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?

[Pol53]

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

[Pol53]

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

Option Compare Database
Option Explicit
 
Private Sub Command7_Click()
Dim myURL As String
myURL = "www.mywebsite/file.xlsx"
 
Dim HttpReq As Object
Set HttpReq = CreateObject("Microsoft.XMLHTTP")
HttpReq.Open "GET", myURL, False, "username", "password"
HttpReq.send
 
myURL = HttpReq.responseBody
If HttpReq.Status = 200 Then
    Set oStrm = CreateObject("ADODB.Stream")
    oStrm.Open
    oStrm.Type = 1
    oStrm.Write HttpReq.responseBody
    oStrm.SaveToFile ThisWorkbook.Path & "\" & "suppliers.xlsx", 2 ' 1 = no overwrite, 2 = overwrite
    oStrm.Close
End If
End Sub
 

[twinnyfo]

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:

Private Sub Command7_Click()
 
    If InternetGetFile(sURLFileName:="www.mywebsite/file.xlsx", _
                       sSaveToFile:=ThisWorkbook.Path & "\" & "suppliers.xlsx", _
                       bOverwriteExisting:=True) Then
        MsgBox "File Saved Successfully!"
    Else
        MsgBox "File Download Failure!"
    End If
 
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!

[ADezii]

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:
   

   Public Declare PtrSafe 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

2. Base Code Definition:
   

   Dim lngRetVal As Long
   '****************************** USER DEFINED SECTION ******************************
   Const conURL = "https://github.com/microsoftgraph/powershell-intune-samples/" & _
                  "releases/download/158068/Intune_Graph_Samples.zip"
   Const conPATH = "C:\Test\"
   '**********************************************************************************
    
   lngRetVal = URLDownloadToFile(0, conURL, conPATH & Mid$(conURL, InStrRev(conURL, "/") + 1), 0, 0)
    
   If lngRetVal = 0 Then
     MsgBox "Download was a success.", vbInformation, "Download Complete"
   Else
     MsgBox "The download could not be accomplished!", vbExclamation, "Download Failure"
   End If

[Pol53]

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

[ADezii]

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:
   

   lngRetVal = URLDownloadToFile(0, conURL, conPATH & "\" & Suppliers.xlsx, 0, 0)

[Pol53]

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 ?

[ADezii]

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:

'If Suppliers.xlsx exists, then DELETE it
If Dir$(conPATH & "\Suppliers.xlsx", vbNormal) <> "" Then
  Kill conPATH & "\Suppliers.xlsx"
End If
 
'Now OK to Download

[NeoPa]

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 ;-)

[Pol53]

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.

[Rabbit]

No worries, the code in the link is pretty much the same method Adezii uses

[NeoPa]

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 :-)