• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 659
  • Last Modified:

MS Access to open a browser enter a URL to download a file then open the file in Excel

In access 2010 I would like to create a macro that will open my chrome browser enter a specified URL which opens a save as window to download a CSV (comma delimited) file(the download is a product of the URL).  Then I would like this macro to then close the chrome browser window and open the CSV file in excel save the CSV  file as an excel file, close excel and then import that excel file into the current database in access. Figured out how do get the "run application" function to work but that is as far as i have gotten.  

Need to do all this because I cannot import the CSV file into Access directly as it has commas in some of the fields.  I does read the csv file correctly in Excel.

Thank you!
0
Zedway
Asked:
Zedway
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
This sounds more like a project requirements list than a request for code assistance. EE is really geared toward helping you overcome difficulties in programming, not providing you with custom programming services.

You can certainly launch a program from VBA using the Shell command, but grabbing control of that once you've got it running is a different matter.

If you need to download file, often you're better off using FTP commands, or an embedded WebBrowser control. This can be controlled (to some extent) through VBA.
0
 
Jeffrey CoachmanCommented:
Also,
This Q might be more appropriate for one of the Web zones.

Please click the "Request Attention" links to ask for assistance.
0
 
ZedwayAuthor Commented:
thank you for your comments I think it may be easier just to download the file.  Sorry about the detailed question I am new to this forum.

How about the excel part of it...? What functions can I enter into the macro builder to open the file save as an excel file from the csv file?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
dqmqCommented:
That's a lot of gymnastics.  Why not just retreive the .CSV into your VBA application.

If your CSV file is under 2MB, you can use an winhttp object to read the CSV file directly into your Access program:

See attached function.

You will need a reference to WinHTTP services if you don't already have it.

If the CSV is larger, other class libraries can be found for retreiving the data.  For example:

http://www.vbdotnetheaven.com/UploadFile/bfarley/FileDownload04182005043858AM/FileDownload.aspx
Private Function HTML_Request(myURL As String) As String

' Create an HTTP object
Dim objHTTP
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

' Download the specified URL
' URL takes the form http://www.yoursite.com/yourdir/yourdir/yourfile.csv
objHTTP.Open "GET", myURL, False
objHTTP.Send

HTML_Request = objHTTP.ResponseText
End Function

Open in new window

0
 
ZedwayAuthor Commented:
Thanks dgmg,

But the File is 140MB...  I have to download it, convert all the data to a template, select the inventory (skus) records i need and then upload.  over 130,000 records with about 35fields.  Not very good yet with VB so do you have an Idea what would work for this size file?  Trying to do everything in access, but i will probably have to get excel to open the csv and then save as excel and then import it into access as it has commas in it and access does not delimit it the proper way.
0
 
Rey Obrero (Capricorn1)Commented:
what version of Access/Excel are you using?
0
 
Rey Obrero (Capricorn1)Commented:
for version 2003 and below
try this codes

Sub saveasXLS()
Dim xlObj As Object, xlFile As String, csvFile
csvFile = "c:\myCsv.csv"
xlFile = "c:\myExcel.xls"
Set xlObj = CreateObject("excel.Application")
    xlObj.Workbooks.Open (csvFile)
    xlObj.activeworkbook.SaveAs xlFile, FileFormat:=43, CreateBackup:=False
    xlObj.activeworkbook.Saved = True
    xlObj.Quit
End Sub
0
 
ZedwayAuthor Commented:
I have version 2010
0
 
Rey Obrero (Capricorn1)Commented:
try this one

Sub saveasXLS()
Dim xlObj As Object, xlFile As String, csvFile
csvFile = "c:\myCsv.csv"
xlFile = "c:\myExcel.xlsx"
Set xlObj = CreateObject("excel.Application")
    xlObj.Workbooks.Open (csvFile)
    xlObj.activeworkbook.SaveAs xlFile, FileFormat:=51, CreateBackup:=False
    xlObj.activeworkbook.Saved = True
    xlObj.Quit
End Sub
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now