VBA - Download Excel File From Internet And Then Extract Data Out Of It
Dec 20, 2012
I'm trying to download an Excel file from the internet and then extract data out of it. The problem is that I don't get any errors, yet the file downloaded is only 1kb in size. The extraction bit works, but the file is empty. The actual file is size 350KB.
Code:
Sub ExtractDataTest()
Dim FileNum As Long
Dim FileData() As Byte
Dim MyFile As String
Dim WHTTP As Object
How to download the zip file from internet explorer and save it into c: using VBA codes.
I have tried many codes from the internet but none of them are working. i am facing problem for the popup box. the macro which i have found was not supporting to it.
I have an excel file which contains 9 columns. Column A contains an article number i.e 12345 . Column B contains name of the product. Columns C and D contain Description and Specifications respectively. Columns E,F,G,H and I contain URL:s of images.
I want to do following.
1. Download all the images in a folder on my harddisk.
2. Copy the text in column C and place it under the text in column D.
note that I am a novice at excel and have never used macros/Javascript/VBA/etc
30 people in different countries use a sophisticated VBA Excel file on their local PCs, let's call it myexcelfile.xls. This file contains no custom data, as all data is stored in myexcelfile_backup.xls.
The biggest challenge with this set up is that whenever there is a newer release or bugfix of myexcelfile.xls, I need to send it out per email to 30 people and hope they replace it with my updated version.
Note that newer versions can replace older versions without any data loss, as all data is stored in the backup file.
Idea: with tools like WGET or BITSADMIN, I was thinking of creating a batch file, that would simply download the latest version from a FTP server, and replace the older version with the newly downloaded one.
Constraints: I shouldn't be using any software that is not native to Windows 7the files can sit in different folders on these 30 computers, therefore the update mechanism would have to identify the path they are in.the update mechanism would have to close the file first before running the update. Question: What is the best way to go about this? A DOS Batch file? Or another excel file that is just the "updater", meaning, it's only purpose is to download the new file from the web and overwrite the old file?
I am looking to import data in text file to Excel sheet. Here is the main catch - All the data are not of fixed length and are in multiple lines as in the example below.
Text File: ID001 Dr Dang 01012011 Male USA Married 123
Below is how I expected the data extraction in Excel: ColumnA ColumnB CardID ID001 Name Dr Dang DOB 01012011 Sex Male Country USA Status Married Pin 123
I am trying to open a website, then for excel to download the csv file, then for it to save it in a specific folder under a specific name and file format (excel).
I am successful at opening the website with the following code, but how to do the rest.
Sub Searchez() Dim IE As Object Set IE = CreateObject("InternetExplorer.Application") IE.Navigate "http://quote.morningstar.ca/Quicktakes/stock/keyratios.aspx?t=clwr®ion=USA&culture=en-CA&ops=clear" 'load web page google.com IE.Visible = True While IE.Busy DoEvents Wend
'IE.Navigate2 "javascript:SRT_keystuts.exportcsv()" 'this is the name of the download link as from when i hover my pointer over download link. End Sub
I have a requirement to download a lot of historical data files from the archieve of an website. The url goes something like this: [URL]....
This downloads file for 21022014 ie. 21-Feb-2014. I need to be able to have a facility to have a selection criteria on my user form (in excel) where I specify a date range and the macro automatically downloads all the valid excel files available within that date range (files for Saturday, Sunday and some holiday dates will not be available in the website archive database) one after the other (like at single clcik of button) into a specific location (predefined viz.. c:/Users/EOD files/) on my laptop. Best would be if I am able to select the save location run time by using a 'Browse' like feature where I go and choose my local laptop folder...same type when we try uploading a file from our laptop to the web..
The code below looks at file names in column A and then goes to a folder and opens and copies the data in range c2 -lastrow from each file and pastes the data into sheet2.
how I could add to the code so that it also inserts the file name in column c?
It would make it easier to track the data in column B.
VB: Sub CopyFromFile() Dim fPath As String Dim lRow As Long
[Code].....
I have attached a sample workbook. The list of file name is in sheet 1. An example of the output is in sheet 2. The data in column A is dummy data generally spans 100's of rows not just 10 as in the example.
The purpose of the code is to be able to put a list of file names in column a in sheet 1 and extract data from those files in a folder. The data extraction works fine. The reson for adding the file names is so that I know what file the data came from.
I have been working on automating IE. Part of my task involves using explorer to download a file from a given url and save it to disk. I've figured out how to launch explorer and start the download, but then a dialogue box appears asking whether to save the file and where to save it. how do I automatically get past this and save the file to e.g "quotes.xls"?
I need to set up an automated process to open an existing .xls file, extract the data from specific cells located in that file and insert it into a totally different file that I have preformatted to accept this data.
Example scenario:
If I have "that_file.xls" and it is unopened, but it contains data in cells "A2 thru F2" that I need to extract... then I have "this_file.xls" , which is open, and set up with all my calculation cells and a specific layout (lets say it has cells "D8, E9, F10, G11, H12, and I13"). I want to be able to add a button to "this_file.xls" to run a macro that would open a file search window where I can select "that_file.xls", and then it will automatically extract the data from "that_file.xls" into "this_file.xls" as follows:
A2 to D8 B2 to E9 C2 to F10 D2 to G11 E2 to H12 F2 to I13
I have the following code that I want to download everyfile at the listed ftp site to a folder on my computer. I was thinking about trying to make a key with dates in thisworkbook sheet1 starting in cell A1 and have it loop through.
Code: Private Declare 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
how can i use VBA tp download a file from a webpage? I've found the following that would display the source code of a webpage. I need to download a zip file and save it to the desktop.What alterations do I have to make?
Code:
Sub Downloadfile() Dim objHttp As Object Set objHttp = CreateObject("MSXML2.ServerXMLHTTP") Call objHttp.Open("GET", "http:\yahoo.com", False) Call objHttp.Send("") Call MsgBox(objHttp.ResponseText) End Sub
How to save a file onto the computer using VBA. The code template below that I found on another forum seems promising, except when I go to the desktop to access it, the .csv file has what looks like the page's source code instead of the actual file I want. This may be because when I go to the url, it doesn't automatically download the file; rather, I am asked to save the file to a certain location (since I don't know the path name of the uploaded file on the site).
Is there any way to alter this code to accommodate this, or will I have to use a different code entirely?
VB: Sub Test() Dim FileNum As Long Dim FileData() As Byte Dim MyFile As String Dim WHTTP As Object
have a link such as www.abc.se/downloadfile?file=12345. When opening this link in a browser the DownloadFile window popups and the file can be downloaded.
I would like to be able to download the file without involving the DownloadFile pop up (since some users have auto open enabled thus the downloadfile pop-up is not always shown)
I have tried URLDownloadToCacheFile and similiar methods but they fail as they dont have the direct link to the file.
So my question now is how can I extract the direct url?
There should be a way to capture it since it will be used in the downloadfile dialog.
I have a program operating in excel 2007 that extracts a picture from a URL and puts it into a PPT slide. I now discover that PPT 2007 doesn't allow this.
So my intent now is to download the jpg into a local file and then import into the PPT.
This is from a couple of sites: [url] [url]
At the top of the code:
Option Explicit
Private Declare 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
in the excel cell if a URL picture needs to be inserted it starts with the prefix "picture=" then the http address.
This did not work.
So I then copied the code from the websites into a module (changing the destination file and URL to mine of course) and called the procedure and still nothing.
complete the attached code (Excel VBA) to automatically process a File Download dialog from Internet Explorer. I want to click "Save" which will produce the "Save As" dialog. Then I need to enter a default filename like (C:Temp) and click "Save" again. When this is working, I will hide Internet Explorer to make the operation completely transparent to the user. The URL is a sample chosen specifically for this test case.
Public Sub Report_Test()
Dim objIE As SHDocVw.InternetExplorer Dim strURL As String Dim varItem As Variant
' Establish connection to the Internet Explorer application. Set objIE = CreateObject("InternetExplorer.Application")
' Set Internet Explorer visible. objIE.Visible = True
I have some code I'm running that works flawlessly when running it in excel. The problem arises when the generic user opens the file via a website I have running, if they chose to run the file in internet explorer rather than saving the file I get the following error:
runtime '1004' error application-defined or object-defined error
The problem occurs in this code:
Code: If Not Intersect(Target, Range("B2")) Is Nothing Then Load UserForm1 UserForm1.Show vbModeless
[Code]....
I have similar code for refreshing specific pages with the "RefreshAll" being changed to "RefreshTower1" for example and I run into the same error.
I have a report that is downloaded off a company database and creates a report that is attached. Is there a way to create a macro to extract some data that I need and leave the rest:
I need to keep and everything in the assigned column PB ID (A3), Issue (B3), Group Code (J3), Group Name (K3), Sub group id (L3), Sub Group Name (M3),Subject ID (L3), Subject Group Name (M3), Subject ID (N3), Subject (O3), PB Status (P3), CPM Report Name (T3), CPM Profile Name (U3), Profile State (V3).
The worksheet's name changes so I can't reference the worksheet. Attached is a copy of what I get when downloaded.
I have my Excel workbook open with 690 files hyperlinked to the specific folder on my documents, if I click one link the folder opens fine with the file I am looking for.
What I need is one of you genius's to invent a code so that I can highlight my 690 files and extract the data from each file and input that into a seperate workbook.
I would get the End of day bhavcopy from the exchange which is a .csv file. The first column contains the column heads like name, open price etc. and the first column. Now I have to extract the names and the corresponding column values from this list by referring to a notepad that contains the ticker names to be extracted, place in another workbook and save that file.
My data looks like this - it's a text file that I import into Excel.
What I'm looking for is operations that require welding, as indicated by the "Weld:" in Row 61. If it is a weld operation, I want to know the time (121.705) and the Operator involved (Opr 2 - Row 64).
I'm simply using an IF statement in a loop to extract these specific cells:
Range("N2").Select Do ActiveCell.FormulaR1C1 = "=IF(MID(RC[-13],11,5)=""Weld:"",LEFT(RC[-13],3),"""")" ActiveCell.Offset(1, 0).Select Loop Until IsEmpty(ActiveCell.Offset(0, -13))
Because I don't know how to write "real" code, it does a similar loop 5 times. Once to pull the sequence number (the first number), then the time, then I have 3 separate columns looking for Operator 1, 2 or 3, respectively. Once those 5 loops are complete there is a pivot table that summarizes the sequences for each operator.
It works fine, but it's slow. Some of the text files have 10,000 lines in them.
I need to extract data from another source file using VBA. I have problems copying the extracted data and format into the required data format. And also, how do i delete the row that is not required in the output file, in the below example: The row, D0, is not needed. An Example Data Format From the SOURCE file:...............
