Vba to extract web data Results

Hello everyone, I'm finally giving up and asking for help. I'm a newbie at VBA and I need help pulling data from a website into excel. I put the code that I already have below. I need to pull the text from <ModelID>724316</ModelID> into "Extract Web Data.xlsm". So I would always like to pull the text (in this case, 724316) into cell A1 of "Extract Web Data.xlsm". Would using one of innertext, innerhtml, outertext, or outerhtml to extract the data work or are those functions for something else?

Sub Extract_Web_Data()

Dim HTMLDoc As HTMLDocument
Dim oBrowser As InternetExplorer
Dim oHTML_Element As IHTMLElement
sURL = "www.website.com"

Set oBrowser = New InternetExplorer
oBrowser.Silent = True
oBrowser.navigate sURL
oBrowser.Visible = True

Set HTMLDoc = oBrowser.document

Do While oBrowser.Busy: DoEvents: Loop
Do While HTMLDoc.readyState <> "complete": DoEvents: Loop

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Name = "Email" Then oHTML_Element.Value = "email"

For Each oHTML_Element In HTMLDoc.getElementsByTagName("input")
If oHTML_Element.Name = "Password" Then oHTML_Element.Value = "password"

For Each oHTML_Element In HTMLDoc.getElementsByTagName("button")
If oHTML_Element.Type = "submit" Then oHTML_Element.Click: Exit For

Do While oBrowser.Busy: DoEvents: Loop
Do While HTMLDoc.readyState <> "complete": DoEvents: Loop

End Sub

Hy guys.

What I need to do is extract data from web page, the easiest way is with web query. But when I stated to learn how to do it, most help is with 'fixed' web pages. Here I have a page, where you enter a company's name and it gives you results (as a phone book). From that page (with results) I need to copy data to my excel worksheet.
I want to do a macro, where you enter a company's name into inputbox, click the button and internet explorer page opens with resulting page. So far I made it ok. But for web query I don't know.

Can anyone help me?
My code is bellow (since it is not an american page, for the ones that will test the code, you should try a search with company's name "ESISTEMI")

Much thanks in advance, Borut

Sub Button1_Click() 
    Dim ie As Object 
    Dim osh As Worksheet 
    Set osh = ActiveSheet 
    Application.ScreenUpdating = False 
    Set ie = CreateObject("InternetExplorer.application") 
    On Error Resume Next 
    ie.Navigate "[URL]http://www.bizi.si/[/URL]" 
    ie.Visible = True 
    Do Until ie.Document.ReadyState = "complete" 
    ie.Document.all.Item("ctl00$ContentPlaceHolderLeft$ucSearchCommon$tbSearchWhat").Value = firma 
    Do Until ie.Document.ReadyState = "complete" 
     '--> here i need web query code
End Sub 
Sub vprasalnik() 
    thesentence = InputBox("Type Text Please", "Using the VBA Input Box") 
    firma = thesentence 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

General question to anyone who is interested... I'm new to VBA and have been working on several macros... one that queries on a sports website to gather certain statistics, and another that extracts data from a financial database for any company I wish to analyze. Both projects are purely for my personal use only. Both websites I access are either public or if a password is needed, I have been previously granted a userID and password. In essence, all I'm trying to do is automate the manual entry of this data to save me some time and prevent manual entry errors.

Today I went back to some of the Terms of Service for some of the websites used and noticed that several of them have language that seems to discourage this practice. And some don't have any language at all discouraging this.

My question to the group is: Has anyone else come across this problem? Is this considered legal? Is this considered ethical? Is this considered "data scraping"; "data mining"; etc? Is this a normal practice?

I apologize if I sound naive (if I do, it's because I am). The last thing I want to be doing is anything that can be construed as illegal or unethical. I appreciate any advice on the subject. Thank you.


firstly I would like to thank you for reading my question. I'm not a profi in programming in Excel, so if there is any mistake in my question, please let me know, thanks.

I have a webpage with a form, where user can enter their login info and a location number.

- Based on these information I would like the location number to be fed into a Macro, when the user was authorized. The Macro then extract required information and give it back as txt-file ( this Macro is already written).
- The result txt-file need to be convert to an excel file and be availabe on web for downloading.
- -> All of this process must be automatically done.

Hello all… I am new to VBA and my experience is by trial and error and learning from it.
I am trying to automate data extraction from internal Web site and now I am stuck.
I have menu items (pic. Attached) and I need to point to and select
The question is what event do I need to create to make this happen?

Here is my code so far... I have to connect to Portal site first for Security reasons and then connect to home site where menu items reside

[SIZE=3][FONT=Calibri]Sub ie()[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]Dim ie As Object[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]Set ie = CreateObject("internetexplorer.application")[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] 'Connect to Portal site[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = False[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]ie.Visible = False[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]ie.Navigate "[B]Sorry… can’t display the URL – This works[/B]"[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]Do While ie.readyState  4[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]Set radio_button = ie.Document.All.Item("promptOrDB")[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]For Each rb In radio_button[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]If rb.Value = "db" Then[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]rb.Checked = True[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]If rb.Value = "prompt" Then[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]rb.Checked = False[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]End If[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]Do While ie.readyState  4[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] 'Connect to HOME Page[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = False[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]ie.Navigate "[B] Sorry… can’t display the URL – This works[/B]"[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]Do While ie.readyState  4[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]ie.Visible = True[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]Do While ie.readyState  4[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri] 'Connect to Component Search Site[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]Application.ScreenUpdating = False[/FONT][/SIZE] 
[SIZE=3][FONT=Calibri]ie.Visible = True[/FONT][/SIZE] 
[B][SIZE=3][FONT=Calibri]THIS Is Where I am stuck - Here I need To point To the menu items And execute.[/FONT][/SIZE][/B] 
[SIZE=3][FONT=Calibri]End Sub[/FONT][/SIZE] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Source Code :

Or here for
Software Component

How do I extract data via a web query, from a web page that requires a password? I'm assuming that there's a way to do it via VBA, but I haven't found it documented anywhere.


A couple of questions:
The first has to do with the following:
Have tried to do a query into Yahoo financials to get the Earnings tables and place in Excel. I can get into the Earnings on MSN easily, but the table formats aren't exactly what I need and the Yahoo format is. I want to use a cell, say A1, that when updated, the query uses that symbol for the query.

In MSN I can use:

http://moneycentral.msn.com/investor...ol='Stock Quote'!$A$1

In Yahoo, the page/set of data I am trying to extract is at:


and I have tried the " 'Stock Quote'!$E$2 " as well as ["DetailTicker"] ( the DefineName range). I also have attempted several other methods of extraction, but just can't seem to get there. These codes give me the "Invalid Symbol" message.

I would suspect that the basic answer to this problem will allow me to get other tables from Yahoo just like the MSN query does.


The second question is: Would extracting data from a web site be best done through a query or via VBA? If I download the tables through a query, I can hyperlink or VLOOKUP easily while VBA I can do the same OR pick up only the data I really want. I guess what I'm really asking is about the "overhead".

Hello all of you Experts,

I have been working (searching the web) on this in chunks for a couple of days and I am stumped. First off, I am a newbie to all this.

Here is my question?

I need to tweak an Macro (currently downloadsa CSV file type from the internet) in an excel workbook to trigger a WinZip Self-Extractor (.exe) located on a server that will run and copy a CSV data source file to a users hard drive.

Hope this makes sense.... here is a text staw dog of the VB code I require.

Using Excel 2003
1. User opens excel (.XLS) workbook
2. Click on Macro button that runs
a. Data.ZIP exe file on ServerReportsPivot_Data_Source
b. Winzip runs and saves extracted file to C:ReportsPivot_Source_Data

Here is the VB code that does work beautifully for downloading files from an URL that was provided by Mav55th on this forum. I just need to figure out how to adjust it to address the ZIP executable (Data.EXE) question above:

Sub Copy_PSD_file_from_SP_Directory()
' PSD = Pivot Source Data
' Copy_PSD_file_from_SP_FC_Prf_Pltfrm
' Code below provided from Excel VBA User Forum (Mav55th)

' Status bar notification
Application.DisplayStatusBar = True
Application.StatusBar = "CSV data source file being copied from SharePoint, please be patient"
Dim i As Integer
Const strUrl As String = "http://enhanced1.sharepoint.com/teams/Pivot_Source_Data/Data.csv"
Dim strSavePath As String
Dim returnValue As Long

strSavePath = "C:ReportsPivot_Source_DataData.csv"
returnValue = URLDownloadToFile(0, strUrl, strSavePath, 0, 0)

' Status bar notification
Application.DisplayStatusBar = True
Application.StatusBar = "Refresh Process completed"

MsgBox "Pivot source data refresh process complete."

End Sub


Dennis' recent OWC posts at Dick's Blog (and some pressure(s) at work) inspired me to have a play with the OWC & SQL Server to render a simple web chart with the help of the Office Web Components.

Documentation on the Office Web Components is pretty hard to come by, but I managed to cobble this together using the following help files found on my hard drive (using Office 2003 Professional)

VBScript Help file C:Program FilesMicrosoft OfficeOFFICE111033VBSCRIP5.CHM

Web Components Help "C:Program FilesCommon FilesMicrosoft SharedWeb Components111033OWCVBA11.CHM"

The code was as follows

HTML Code:

       A Simple Page 

	A Sample ADO Chart form Joey (pdunity)....

This is a sample chart that uses Office Web Components to display the results of a SQL query
You can view the relevant source code by selecting VIEW > SOURCE from the IE toolbar

Sub Window_OnLoad() Dim rs, categories, values, c, stSQL ' This example connects to pdunity on Joey ' and charts the Query "SELECT SUM(balance) as TBal, type FROM dmd Where balance > 0 GROUP BY type". ' ADO is used to open a connection to the database and return the entire recordset. The query ' contains two columns: Balance & Type. ' The records are then extracted into strings. categories = "" values = "" stSQL = "SELECT SUM(balance) as Bal, type as Type " stSQL = stSQL & "FROM dmd WHERE balance > 0 " stSQL = stSQL & "GROUP BY type ORDER BY SUM(balance) Desc " ' Open the connection and execute the query. ADOConnection1.Open "Provider=sqloledb;Data Source=JOEY;Initial Catalog=pdunity;Integrated Security=SSPI;" Set rs = ADOConnection1.Execute(stSQL) ' Start at the first record and move through the entire recordset. ' Field 0 is the Balance, Field 1 is the type. ' Create a tab-delimited string for the names and one for the values. With rs .MoveFirst Do While Not .EOF categories = categories & .Fields(1).Value & Chr(9) values = values & .Fields(0).Value & Chr(9) .MoveNext Loop .Close End With ADOConnection1.Close ' Remove the leftover tab character at the end of the strings. categories = Left(categories, Len(categories) - 1) values = Left(values, Len(values) - 1) ' Create a chart with one series (called "Balances"). With ChartSpace1 .Clear .Charts.Add Set c = .Constants ' Enable the title for the chart workspace. .HasChartSpaceTitle = True .AllowFiltering = True ' Set the title for the chart workspace. .ChartSpaceTitle.Caption = "Demand Account Credit Balances by Account Type" .ChartSpaceTitle.Font.Bold = True With .Charts(0) .SeriesCollection.Add With .SeriesCollection(0) .Interior.Color = "Orange" .Caption = "Balances" 'Set the series categories and values using the strings created from the recordset. .SetData c.chDimCategories, c.chDataLiteral, categories .SetData c.chDimValues, c.chDataLiteral, values End With ' Set the chart type and format the numberformat of the axis. .Type = c.chChartTypeColumnClustered .Axes(c.chAxisPositionleft).NumberFormat = "#,##0" End With End With End Sub
Anyone familiar with VB and VBA will see that the code enclosed within the script tags following Sub Window_OnLoad() is pretty familiar stuff if you have used ADO connections within VBA.

The following lines

set the object references to the ADO and OWC objects.... these reference id's can be found in the relevant help files (they differ by Office Version)

Obviously you will need to amend the SQL and ADO ConnectionStrings to match your SQL Server set up however I hope that this will illustrate how this method can be achieved.

Hope this helps someone


I'm writing vba code in excel to navigate web pages in order to then extract
data from the web pages. I've had good luck so far with basic controls on
web pages. I can enter website login information and have figured out how to
select tabs, but now I have a listbox with filters for pages that I can't
get past. The listbox -- when you click on it normally with the mouse, up
pops a list of filters like "Last Day", "Last 7 Days", etc. as filters for
the specific data to display.

Thus far I've been using code like

Set ipf = ie.Document.all.Item("password")
ipf.Value = "(pword)"
Set ipf = ie.Document.all.Item("LoginForm")

where ie = CreateObject or ShellWindows.Item to recognize the web page. So
far I've identified Document.all.Items by finding "name=" and "value=" and
"id=" in the html code. With the listbox, I've found the name of the Filter
Item and its options for values, but since it's not a form, I can't find out
how to activate the selected option. Any ideas what html code I should be
looking for?

Can anyone help?



I've excel workbook containing vba, this workbook does some complex
calculations when some data is send to it (or paste) these calculations
are based on vba as well as functions.

My problem is that i've to run this on web server (as COM component)
send some data to it and extract data.

I'm thinking on these lines, open excel template file (visibility
should be hidden) send some data to it, perform calculations, extract
data from it and generate word and pdf reports based on extracted data.
Now i know using word and excel as COM is problem on server as it may
hang in memory and not easy to clean efficiently.

I need expert advise from you guys to :
1. Summarize the process in efficient manner (maybe alternate solution)
2. Risk associated and to bypass it
3. and overall feasibility of the solution

I'm using ASP.NET on server


Hi, I'm new to this Forum and VBA for that matter. I was wondering if someone could help me out with what is probably simple question to answer. I have produced a Macro based on the following youtube video:


I have several hundred URLs listed in column A of sheet 1. Here are three typical examples:


At the moment the macro copies data from each of the resulting webpages and pastes it into a new worksheet. See VBA code below. Instead of pasting the data to a new worksheet, what I need the macro to do is paste the data from all the URLs to a specific worksheet, say sheet 2. For example, the data from the 1st URL would be pasted to cells A1:H230, the data from the 2nd would be pasted in the same worksheet to cells A231:H388 and so on. The macro needs to continue extracting and pasting data until there are no URLs remaining (i.e. there is a blank cell).

Please note I have a very basic knowledge of VBA and would prefer the answer to include the revised code and a simple explanation of the changes made.

The current VBA code I’m using is as follows:

Sub Loopthrough()
Dim WSO As Worksheet
Set WSO = ActiveSheet
For Each Cell In WSO.Range("A1:A3")
ThisURL = "URL;" & Cell.Value

With ActiveSheet.QueryTables.Add(Connection:= _
ThisURL, Destination:=Range("$A$1"))
.Name = "tip067.shtml"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Next Cell
End Sub

Hello - apologies if i have given this a wrong title (please feel free to amend)

I have extracted a report from a web package into Excel - basically a list of documents held on our website. The Cells in Column B would have the word Title or Description, Column C would have the actual title of the document, Column D would be blank on rows with Title in Column B and Column E has a Document ID (DocID)Number.

To make this easier to read is it possible to filter Column B using Title and then move the DocID number from Column E to Column D.

I have attached a shortened version of the spreadsheet with the filter applied in Column B already. The real spreadsheet is 1000 lines long and would take quite a while to drag and drop each DocID number from Column E to D.

I assume this can only be done with VBA so if anyone has any suggestions i would really appreciate it.



Hi all,
I have a workbook containing ~60 worksheets in which I would like to hide
~50 of them but have individual worksheets open on demand. The worksheets
contain hyperlinks to other worksheets within the same workbook and are
simply referenced as an Excel hyperlink. On clicking a hyperlink I would
like it to unhide the relevant worksheet, on hitting the Excel Web 'back'
button I would like it to rehide the relevant worksheet.
Is this feasible as currently constructed or am I looking at additional
macros to replace the hyperlinking?
Extract of the code doing the worksheet hiding:
Private Sub Workbook_Open()
On Error GoTo ErrorHandler
Dim wst As Worksheet

'Ensure the Data sheet is hidden and can't be shown without password
ThisWorkbook.Worksheets("Data").Visible = xlVeryHidden

'Ensure the Exchange Rate sheet is hidden and can't be shown without
ThisWorkbook.Worksheets("ExchangeRates").Visible = xlVeryHidden

'protect the Worksheets from alteration
For Each wst In ThisWorkbook.Worksheets
wst.Protect (PWORD)
Next wst

'Unprotect the Exchange Rate Worksheet to obtain Reuters updates
ThisWorkbook.Worksheets("ExchangeRates").Unprotect PWORD

'activate the MainMenu worksheet

'define the data range names

'sort the specifed data ranges
SortDataRanges "Data_AssociatedDealerGroup", "Data_DealerNames",
SortDataRanges "Data_AssociatedUnitCode", "Data_RMCodes"
SortDataRanges "Data_RMUnitCodes", "Data_RMUnitNames"
SortDataRanges "Data_AssDealerGrp", "Data_AssMarginTempl"

Exit Sub
DisplayError Err.Number, Err.Description
Resume Exit_Workbook_open
End Sub
Any help will be greatly appreciated - I have read through the other
discussion threads, but I don't see anything specific to my requirements.

I have an Excel spreadsheet with reimbursement/expense data that I need
to submit to my main office. The only way to do this is by filling in
a web form for each reimbursement/expense entry. Can you help me with
VBA or VB code that can extract data from Excel and paste it into an IE
web form?

I just started dabbling with VB 2005, and I am quite proficient with
VBA so any help could go a long way!


I searched the googles groups to get some insight into this. But I could
not get exactly what I want.
I know some experts do not recomment using <sendkeys>

however I want to get data from a webpage into excel . It is is not simple
using <new web query>. In the webpage I have to click one of the two
option(or are they checkboxes?) boxes and then enter a text in the small
window. finally I have to click something like a command button <submit>

I do have some codes wherein you have to simply enter the user id and
password in the code and run it. .
The EXTRACT ONLY of the sub is
While appIE.busy
appIE.navigate "https://access.leggmason.com/"
SendKeys "excel", True '----------this is user id
SendKeys "{TAB}", True
SendKeys "hello", True '------------this is password
SendKeys "{ENTER}", True

Of course this code also gives problem at READYSTATE_COMPLETE but I used a
variant dim for this.
it works in some cases but it does not work in some other cases where due
to security reason the webpage asks again for the password(example-yahoo
mail). I tried to modify the same code in the mouse clicking case also. B
ut I am stumped as I could not find the keyboard short cut for clicking the
mouse. In the case of <submit > button i presume that I can use <enter> key.

It is obvious I have not understood fully the sub

Highly thankful for any suggestion how to go about . I am fairly familiar
with excel and VBA.
mine excel 2000/windows 98 SE
the relevant url is


Hi guys. First of all, i want to apologize for my bad english. i understand very well, but it is dificult for me to say correct what i want.

I have this error in my Excel file, when i run a macro:

I use Microsft excel 2010 with Sp1, x64 version. I have Windows 7 Ultimate. I try my excel file on other system, with x86
default Excel 2010. The same problem.

I will explain in few words what my macro doing:

I extract some data from the internet website, using a link witch my macro open with webquery, and copying the data from internet in same worksheet. Then, do it again, until no more link are.

I will post here the code, maybe you can find were is the problem:

Sub NewLeague()
    Application.ScreenUpdating = False
    Dim AnteYear As Worksheet
    Application.Run "clear"

    'denumire link nou
    linktext = InputBox(Prompt:="Link name", Title:="LINK", Default:="New link here")
    'copiere link in sheetul curent
    Sheets("Season").Range("K2") = linktext
    'refresh Results
    With Sheets("WEB").Range("A1").QueryTable
        .Connection = "URL;" & linktext & "results/"
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingAll
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = True
        .WebDisableRedirections = True
        .Refresh BackgroundQuery:=False
    End With
    'copiere results in anul curent
    Set FoundCell = Sheets("WEB").Range("H1:H700").Find(what:="*",
after:=Range("H700"), LookIn:=xlValues)
    If Not FoundCell Is Nothing Then
        FirstRow = FoundCell.Row
        Set FoundCell = Sheets("WEB").Range("H1:H700").FindPrevious(after:=FoundCell)
        LastRow = FoundCell.Row
        Sheets("WEB").Range("H" & CStr(FirstRow) & ":L" & CStr(LastRow)).Copy
        Sheets("Season").Range("B4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End If

    'sortare meciuri dupa data
    ActiveWorkbook.Worksheets("Season").Sort.SortFields.Add Key:=Range("B4:B700"),
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Season").Sort
        .SetRange Range("B4:F700")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    Application.Run "Odds"
    Application.Run "AH"
    Application.Run "Totals"
    Application.Run "UO"
    Application.ScreenUpdating = True

End Sub
Here i think is OK.

The all 4 macro witch i calling in this VBA code are practically the same, but the link URL adress is diferent, so i need to create these 4 aditional macro code.
Sub AH()
    Dim AHtext, UOtext, gamelink, linktext As String
    Dim AHlink, UOlink, Oddslink As String
    Dim lastline As Long
    Application.ScreenUpdating = False
    'copiere link in sheetul curent
    linktext = Sheets("Season").Range("J2")

    'copiere meciuri din results
    lastline = 3 'ultima linie in anul curent
    For Index = 1 To 1000
        AHlink = Sheets("WEB").Range("R1").Offset(Index, 0)
        If AHlink <> "" Then
            AHlink = Sheets("WEB").Range("R1").Offset(Index, 0)
            With Sheets("Asian").Range("A1").QueryTable
                .Connection = "URL;" & AHlink & "/"
                .WebSelectionType = xlEntirePage
                .WebFormatting = xlWebFormattingNone
                .WebPreFormattedTextToColumns = True
                .WebConsecutiveDelimitersAsOne = True
                .WebSingleBlockTextImport = False
                .WebDisableDateRecognition = True
                .WebDisableRedirections = True
                .Refresh BackgroundQuery:=False
            End With
            'copiere meciuri
            Sheets("Asian").Range("AA1").Offset(lastline, 0).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
            lastline = lastline + 1
        End If
    Next Index
    'sortare dupa data
    ActiveWorkbook.Worksheets("Asian").Sort.SortFields.Add Key:=Range("AA4:AA750"),
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Asian").Sort
        .SetRange Range("AA4:AH750")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
    Application.ScreenUpdating = True

End Sub
This is one of the 4 macro witch i'm calling.

Now, when the error apear ?

Sometimes, after the first calling macro, sometimes, after the second macro, sometimes after the third macro, sometimes all did well, with no errors.

When my file crashed, i can exit, choose restart or close the Excel file.
When i choose restart, the Debug apear, and the code line
 was highlighted with yellow. I think here is the error, but i couldn't find the correct solution.

Thank in advance !


I have a new job where I have to go to take information from webpages and paste them onto excel. I tried my best to sift through tutorials and guides using Google but couldn't find any cases that were similar enough to my own.

What I want specifically is, from the list of museums on this webpage, I want to copy the name of the museum, the address, and the phone number. All of these can be accessed by clicking on the links, but not before.

(This is the list from the province of Manitoba, but I will eventually need every province.)

I know it is possible to use web queries to take information from within each link, however that is still tedious because of the number of links I'll need to enter eventually. Also, I cannot identify the pattern that the url changes by from museum to museum.

I have little to no VBA experience and would greatly appreciate any tips or advice on how to do this, if it is possible. I've looked into free web scraping softwares, but I cannot find one that can extract data from within links, rather than on 1 page.

Thanks in advance.

Hi... not sure if this can be done the way I'm hoping, but I'm trying to write or find some vba code I can use to copy the source text behind a web page.

The page is a financial market data page, and is password protected. I CAN launch and logon to the page with no problem. The page contains a tree control in a left side pane, then the contents of whichever item is select on the right. About 3 levels down into the tree is a page with a series of reports that I need to import to Excel.

These pages are produced daily - each of which has a date and unique identifier that can be pasted directly into the browser's address bar. So on Monday I could use https://www.abc.12345, then Tuesday www.abc.12346, etc.

The problem is, these identifiers are in no discernable or predictable pattern. I cannot open the page directly in excel, nor can I use the Import Data from Web function (2007) ... results are simply a blank page.

What I thought I could do, then, is automate the procedure that obtains the source code, which I can parse and look for the current date. Once I have the line with the current date, I can extract the unique identifier, then paste it back into a string and resubmit to the browser.

I just can't figure out how to get to the source code... anybody out there have a way to get to it? Since this is going to ultimately be distributed to 20 or so analysts in different countries, I don't think I can use other tools (like the HTML Extractor from Iconico).

I've attached a screenshot of the page, just in case it helps clarify.

Thanks for any help!

Hey guy's im new here and my second question here starts off quite difficult.

First off:
My programs : Office 2010
My skills: limited ( i've been searching the web for quite some time now and leand a view things from what i've read)

My question:

For my work i want to create ( i was asked to do ) an database of machine testing specs.
They have 200/300 word documents with an table in it with the specs.
I want so import this info from the word document into an excel spreadsheet. ( easy data comparison and so on )

I've already asked the question how to automate the proces of exporting the data from word to an excel file ( in the word and excel help forum )
I think those guys can help me, but you guy's to ( its more excel then word vba ) so i'm asking the same here.

I've got an working macro for data extraction form the word talbe in to excel via VBA.

     'Imports cells (3,2) and (4,2) from Word document Tables 1-10
    Dim wdDoc         As Word.Document 
    Dim wdFileName    As Variant 
    Dim TableNo       As Integer 'number of tables in Word doc
    Dim iTable        As Integer 'table number index
    Dim iRow          As Long 'row index in Excel
    Dim iCol          As Integer 'column index in Excel
    wdFileName = Application.GetOpenFilename("Word files (*.doc*),*.doc*", , _ 
    "Browse for file containing table to be imported") 
    If wdFileName = False Then Exit Sub '(user cancelled import file browser)
    Set wdDoc = GetObject(wdFileName) 'open Word file
    With wdDoc 
        TableNo = wdDoc.tables.Count 
        If TableNo = 0 Then 
            MsgBox "This document contains no tables", _ 
            vbExclamation, "Import Word Table" 
        ElseIf TableNo > 10 Then 
            TableNo = 10 
             'Else TableNo is actual number of tables between 1 and 9
        End If 
        Range("A1") = "Table #" 
        Range("B1") = "Cell (3,2)" 
        Range("C1") = "Cell (4,2)" 
        For iTable = 1 To TableNo 
            With .tables(iTable) 
                 'copy cell contents from Word table cells to Excel cells in column B and C
                Cells(iTable + 1, "A") = iTable 
                Cells(iTable + 1, "B") = WorksheetFunction.Clean(.cell(3, 2).Range.Text) 
                Cells(iTable + 1, "C") = WorksheetFunction.Clean(.cell(4, 2).Range.Text) 
            End With 
        Next iTable 
    End With 
    Set wdDoc = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This macro works for me, but it will only get the data from 1 file ( via selection ) ,
I want it to do so automaticly ( and for later document to ) and i thought that i've it save the data to an single excel file is the best thing.

So: The extracted data from 1 word file is saved in 1 excel file.

And then later on i import the data from all of my excel files in to 1 master (database) excel file.
Preferbly via VBA Macro

Since i've not used macros or vba before it thought that was the best for me. And easyer to understand.
And dont get it all in one file with a huge macro wich is difficult for me to understand multiple steps.

I've already made an seperate excel file with the cell names that contains the data i need.
And made an range (with titles i want to have)
So i can copy it into the macro ( easy )

The code above works, and when i copy my ranges and cell info it works. But give's me an error on empty ( there i dont have the basic knowlegde to overwrite that )
I know it has something to do with the cleaning cell option. But dont have the knowlege ( also didnt have the time to find out ) how to overcome that.
For you it sure will be an easy task.

I hope i've been clear enough of what i want, i've not then please ask.

Thanks in advance!!!

Edit: The macro contains a fault,, i've changed the first line :Dim wdDoc As Word.Document
and changed Word.Document to Object to make it working