473,561 Members | 3,128 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

MS Access Command Button To Download File

27 New Member
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 4209
twinnyfo
3,653 Recognized Expert Moderator Specialist
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,564 Recognized Expert Moderator MVP
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 New Member
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 Recognized Expert Moderator Specialist
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
Rabbit
12,516 Recognized Expert Moderator MVP
https://bytes.com/topic/access/insig...rnet-using-vba
Jun 17 '20 #6
twinnyfo
3,653 Recognized Expert Moderator Specialist
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 New Member
Thanks a lot for the suggestion Rabbit....unfor tunately it sailed about a mile over my head. I'm not very knowledgeable when it comes to VBA
Jun 18 '20 #8
Pol53
27 New Member
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 Recognized Expert Moderator Specialist
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

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

Similar topics

2
7604
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 check box s checked. The commend button name is command36 and the check bx feild is named . I have written the following code: IIf Me! = True,...
14
4951
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 for things like delete, save, edit, cancel buttons - in the footer, or on the form detail section? 2. If in the footer, how do you add them to the...
24
5330
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 ( surName == "abc") then open table Table_Abc if ( surName == "qaz") then open table Table_Qaz i will have a lot of surnames, so tables needs...
1
10473
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 trying to export to Excel using a command in an Access Form. RowID strFY AccountID CostElementWBS 1 2008 1 ...
3
2280
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 obtains data from the Access database and I have set the refresh on open option in the pivot table options. Am i missing something in the code ?
2
1843
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 Seach's site which identifies the file type, opens the document in its correct program, and prints it:Public Const SW_HIDE = 0 Public Const...
1
4127
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 the info and auto sending the task for other forms, but I can't seem to just open the new task and allow user input. Any advice?
5
5872
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 the screen. I have been unable to find any way to disable this button in Access 2007 and subsequently I have been forced to find ways to detect and...
1
6248
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. The VB to browse for the file is as follows Private Sub CmdBuildingAdd_Click() Me!txtSelectedFile = BrowseFiles()
7
2174
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 trigger called add_to_total2. The trigger on insert adds the AMNT to the total payment in another table. My issue is when the users enter data...
0
7644
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7568
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7928
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6206
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5469
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3615
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3598
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2066
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1180
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.