473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

MS Access Command Button To Download File

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
Jun 17 '20 #1
18 4141
twinnyfo
3,653 Expert Mod 2GB
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.
Jun 17 '20 #2
NeoPa
32,556 Expert Mod 16PB
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.
Jun 17 '20 #3
Pol53
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 ?
Jun 17 '20 #4
twinnyfo
3,653 Expert Mod 2GB
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.
Jun 17 '20 #5
twinnyfo
3,653 Expert Mod 2GB
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?
Jun 17 '20 #7
Pol53
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
Jun 18 '20 #8
Pol53
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.  
Jun 18 '20 #9
twinnyfo
3,653 Expert Mod 2GB
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!
Jun 18 '20 #10
ADezii
8,834 Expert 8TB
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
Jun 18 '20 #11
Pol53
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
Jun 19 '20 #12
ADezii
8,834 Expert 8TB
  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, 52 views)
Jun 19 '20 #13
Pol53
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 ?
Jun 19 '20 #14
ADezii
8,834 Expert 8TB
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
Jun 19 '20 #15
NeoPa
32,556 Expert Mod 16PB
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 ;-)
Jun 20 '20 #16
Pol53
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.
Jun 21 '20 #17
Rabbit
12,516 Expert Mod 8TB
No worries, the code in the link is pretty much the same method Adezii uses
Jun 21 '20 #18
NeoPa
32,556 Expert Mod 16PB
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 :-)
Jun 22 '20 #19

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

Similar topics

2
by: Sam | last post by:
Trying to change the visible property on a command button (access97) based on a record felid on the form. The felid is a check box felid and I am trying to make the button disappear with the...
14
by: Kevin | last post by:
A couple of easy questions here hopefully. I've been working on two different database projects which make use of multiple forms. 1. Where's the best/recommended placement for command buttons...
24
by: questionit | last post by:
Hi I am new in Ms Access I need to know how can i create a command button on a form. When it is clicked, a table is opened that is associated to the data in a field i have. E.g: if (...
1
by: CoolFactor | last post by:
MY CODE IS NEAR THE BOTTOM I want to export this Access query into Excel using a command button on an Access form in the following way I describe below. Below you will find the simple query I am...
3
by: gra | last post by:
Hi Access 2002 I have a Command Buton using some VB code to open an Excel spreadsheet. However, when the spreadsheet opens it doesn't refresh the data. The spreadsheet is a pivot table which...
2
rsmccli
by: rsmccli | last post by:
Hello. Using Access 2002. I have set up a command button on a form that will print off a list of hyperlinked documents that resides in one of our tables. I have been using code borrowed from Graham R...
1
by: tymperance | last post by:
I have a 2007 database that I need to add a command button that will open a new Outlook task and allow the user to input the assignment, start date, due date, etc. I've got plenty of code scripting...
5
by: Tony | last post by:
I am continuing to develop an Access 2007 application which was originally converted from Access 2003. In Access 2003 I was able to disable the Access Close button in the top righthand corner of...
1
by: alnino | last post by:
Hi, On a form I have a command button that allows a user to browse for a file (PDF, Word, etc…). This command button then stores a hyperlink to the file within an associated txtfield in the table....
7
by: bherring | last post by:
I have a form called allpayments that allows for data entry to a table called CUST_PAYMENTS. On the form I only have certain fields from the table. PAT_ID, TRNSDT, TRNSCD, AMNT. now in SQl I have a...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.