Free Microsoft Excel 2013 Quick Reference

Vba get data from web Results

I am writing an application which uses Excel to fetch and update data
from a database. I have been able to get the data from the WebService
and get it into Excel...but I have not yet found a way to the data back
to a WebService.

The architecture I am using is:
Excel 2003 workbook (with VBA)
--includes SOAP 3.0 and MS Web Services Toolkit 2.01
WebServices (via C#/.Net running on an IIS web server)
MS SQL 2000 database (that the WebService attaches to)

The webservice which I get the data from is (in C#):

---begin---
[WebMethod]
public DataSet GetHistoricalDoubleWeibullModelParameters(string
fileLocation) {
DataSet ds;
....
return ds;
}
---end---

In Excel I am able to receive this code using:

---begin---
Dim weibullWebService As clsws_WeibullWebService
Dim historicalDataSet As MSXML2.IXMLDOMNodeList
Set weibullWebService = New clsws_WeibullWebService
Set historicalDataSet =
weibullWebService.wsm_GetHistoricalDoubleWeibullModelParameters("parameter")
---end----

However, I want to send this "historicalDataSet" back to a webservice
in the form of a dataset.

The webservice I want to send it to is:
---begin---
[WebMethod]
public double DesignWindSpeed_DoubleWeibullModel(DataSet weibullData,
double targetReturnPeriod) {
double x;
....
return x;
}
---end---

I have tried using the code in Excel (which I know is wrong):
---begin---
windSpeed =
weibullWebService.wsm_DesignWindSpeed_DoubleWeibullModel(historicalDataSet,
50)
---end---

When I try to run macro that this is part of, the call breaks due to a
casting error. What I can't figure out is how to convert a
IXMLDOMNodeList object into a whatever Excel 20003 VBA will send as a
dataset over SOAP for my webservice.

What do I need to here to make this conversion?

Any suggestions would be welcome gjn[at]rwdi.NOSPAM.com

Graham

I am using Excel 2007 SP1, on Vista Enterprise 32 bit. I have been researching through Google (and tried search here). Perhaps it is more difficult than what I want to hear, but what are my options to consume a web service from Excel 2007.

Based on my research, this used to be accomplished from VBA. I can't tell if this option no longer is viable. I read at one place that it is, but that WS-security is not supported.

But then, I also read this:

I had a conversation with a client that asked "Where do I get the latest SOAP toolkit for Vista?". I had to politely say "We don't support the SOAP Toolkit anymore." Which was followed by "Well how do I call a web service from VBA in excel?". The short answer is "you migrate to managed code!" and use Visual Studio 2005 Second Edition (VSTO) or the new Visual Studio 2008 Excel Workbook project.
I would like to have a sheet that has 2-4 input fields and a 'submit' button. I need Excel to interface with a web service, passing data from the input fields, and displaying data that is returned.

Is this possible in a somewhat straightforward manner?

Hello all,

If any one can help me to write a VBA macro for excel, where i could get values from a web site?

URL is http://www.peacesoftware.de/einigewerte/co2_e.html

where PRESSURE and TEMPERATURE are needed to get values from the table.

Is there any option to get it done by simply putting numbers in excel and run a macro so the values for spesific temp and pressure are calculated using this website?

Thank you for your help!

Hi all,

I've tried searching for this and can't find what I'm looking for.

I'm in the process of exporting data from a Web Based Application using the Apache POI library. I can fully recreate what I need to do using the Apache POI interface, but it is too memory intensive for the server and is causing problems.

So we're going to put it on the user to run an embedded Macro to do all of the formatting that they need.

I have a set of simple Macros that I'm working on that will format data cells within a range and then do a conditional format beyond that. The problem is that I need this to be dynamic as the data will change over time.

So what I need the Macro to do is:
Sub BackgroundFill()
'
' BackgroundFill Macro
' Macro to Change the background color.
'
    Range("B2:I3835").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With
End Sub
But where the Range is selected, I need it to select all the data in those columns from Row 2 to Row N where Row N is the last row of data and then go 1000 Rows beyond and format the columns that way.

Sub ConditionalFormat1()
'
' ConditionalFormat1 Macro
' Macro to Record the Conditional Formatting for Column B.
'

'
    Range("B2").Select
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(B2<>"""",COUNTIF(categoryF,B2)=0)"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = 0.799981688894314
    End With
    Selection.FormatConditions(1).StopIfTrue = True
    Selection.AutoFill Destination:=Range("B2:B3835")
    Range("B2:B3835").Select
End Sub
There will be additional Conditional Formats for Columns C through I as each is based on its own formulas but the range here should also go to the last row + 1000 rows, but you should get the idea...

Any help would be appreciated and thank you in advance!

Chris

I encountered some problems while doing my excel vba. Please give some enlightenment and guidance along.

Overview of what i am doing
I have a workbook with 3 worksheets. In each worksheet, there is a web query trying to get data from a webpage. The web query is at Cell A1 of each worksheet. The query is supposed to autorefresh every 5 minutes and i will have some code running through the cells to get a few values in each sheet.

The checking code (call check) will be called every 3minutes.

If the value is greater than a threshold then pop out a message box stating the value. When this happen, the message box needs to be focus/pop up in front of the screen no matter what the user is doing (checking microsoft outlook, browsing web using Internet Explorer or other activity).

Problem I have encountered:
1) I needed to password protect the workbook in case the user accidentally remove or change the cell A1 which the data is inserted into.

But i realised that the auto refresh for the web query will not work if the sheet is protected.

thus i use the following code> unprotect each sheet in the workbook then refresh the query table
For Each Worksheet In Workbooks("Datacheck").Worksheets
Worksheet.Unprotect Password:="xxxxxxxx"

On Error Resume Next
With ActiveSheet
Range("A1").QueryTables(1).Refresh
End With
On Error GoTo 0
Next Worksheet

Qn: Is there a better way to go about refreshing the web query in a protected sheet beside the code above?

How do we give a index to the querytable (i am not too sure as i imported the data using record macro method and i believe they didnt assign an index to the webquery). QueryTables(1) refering to querytable with index 1?

Another problem> Msgbox pop up:
After I run the call check macro, when the cells value exceeded a threshold, a msgbox should pop out in front of the screen. This is very important and crucial for the msgbox to grab the attention of the user.

As this excel program will be running for 12-20 hours each day, I needed the msgbox to grab the user attention every time the cell values exceeded threshold (currently i am running the check subroutine every 3 minutes which is still sufficent timing for the user to react or immediately which is better still).

I had use the following code in my program to cause the message box to pop up in front of the screen. But there is a few occurences that the msgbox did not pop up in front of the screen (the message box did trigger behind the excel but it did not force shown in front of the other application i am running like Internet explorer, microsoft outlook and so on ). The excel icon in the taskbar did not flash also.

Method 1 (a separate subroutine)
Sub Test()
Workbooks("Datacheck.xls").Activate
Application.WindowState = xlMaximized
Application.OnTime Now + TimeSerial(0, 0, 2), "check"
AppActivate "Microsoft Excel"
AppActivate Application.Caption
End Sub

Method 2 (after the if statement that check the cell)
If error1 = True Then
Workbooks("Datacheck").Activate
AppActivate "Microsoft Excel"
AppActivate Application.Caption
End If
both Method 1 and 2 will sometimes miss out on the pop-ing of msgbox to grab user attention

Qn: I needed the message box/the workbook to be really shown/pop up in front of the user when the value exceeded threshold. If there any other reliable method to do this? Missing the message box might cause serious problem for my work.

Another problem> Checking based on timing
I need to perform checks on the cells in the worksheet. For example if
the threshold is 60 between 8 am to 8pm and threshold is 40 between 8pm till 8am

Needed to add a timing condition in the if else also. How can we do it?
If Sheets("Sheet1").Cells(total(count) - 1, 12) > 60 Then
error1 = True
End If
Qn: Currently i am using trying to let the macro check run every 3 minutes using this statement
Application.OnTime Now + TimeSerial(0, 0, 2), "check".

Is it better if i use worksheet change event for this case? If i use worksheet change event and place the call check statement in there, is it as soon as the cell value changes above threshold, the check routine will execute?

Qn: Since i am refreshing the 3 web query every 5 minutes and the check procedure is run every 3 minutes. Alot of activity is going on in this workbook. I also have other excel workbooks running doing other stuff, I realised that while working with other worksheet, it tends to be very laggy at times. I believe it's caused by the activities going on in the datacheck workbook. Any work around for this?

Hi, am i glad to find this forum!

I know only how to write functions in VBA, but not do complex VBA codes so here's a kind of problem i'm facing:

I'm trying to create a code to collect MONTHLY closing price stock data from a certain time period X(march 2007 for example) which i can input, till the last month today (october 2010), but i'm trying to make it refresh itself so that every time i click the macro button, and suppose it's november 2010, so it could i think drop all the cells down and then paste the november 2010 monthly data above?

Here are the problems i ran into:

MSN money central seems to be giving me only the quotes from today (live quotes, but not a summary of all quotes from t-1, t-2, t-3 etc)

Yahoo finance seems to give me for some stocks dividend dates which seem to screw up the data a little (but i can correct this part). Here's the problem i can't make it work automatically, i have to manually go to the web query, type the stock ticker in yahoo.finance.com and then get the data, i can't manage to get an automatic way in which i just type the stock name into the excel bracket and then get the data, nor can i automatically manipulate the months or dates for the download.

If you can help me show how to do this for monthly closing prices, i can then do this for yearly and daily open/close/high/low prices etc, which can be really helpful for other people too i think.

Here's my excel file attached

Task:

I want to create a macro to down load data from a website each day and place it into excel. The difficulty is that the data is contained with a different file each day, so I want the full URL to also change each day.

Problem:
When I created a macro of a webquery, the URL link is hard coded in the VBA code. Instead I would like the code to reference a spreadsheet cell where the URL can change. i.e. "A2" will conntain the weblink instead.

Currrent VBA code (extract):

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;ftp://user:password@ftp.website.com/download/1110d_26.05_", Destination _
:=Range("D20"))

I have played around trying to replace the web address with a cell reference but cannot get it to work. Can someone please help?

Thanks.

I have a worksheet with a list of web hyperlinks (1887 to be precise)!

My VBA code is supposed to go through the list, perform WebQuery on those links and return certain data from visited pages!

However when I run the code I get:

Run-time error 1004

File could not be accessed. Try one of the following:
- Make sure the specified folder exists
- Make sure the folder that contains the file is not read only
- Make sure the file name does not contain any of the following characters: < > ? [ ] : | or *
- Make sure the path/file name does not contain more than 218 characters or if I try to execute a single step manually I get: Invalid Web Query

Of course, none of the above mentioned reasons are true. The links are all valid and when copy-pasted either in Web browser or in Excel's dialog box for "Get Web data" the page loads.

What's even more irritating is:

1. the same code works perfectly when the file is saved as Excel 2003 book and run on 2 other computers!

2. the same code isn't working on all or nothing principle. It manages to perform WebQuery on some links and not on others. Links downloaded are DIFFERENT each time the code is run.

The problem was addresed, but not solved in this thread:
http://www.ozgrid.com/forum/showthread.php?t=64645

SO, HOW THAT BRILLIANT NEW EXCEL 2007 IS TWEAKED BY IT'S CREATORS TO RANDOMLY CUT YOUR ACCESS TO WEB DATA?

Assignment will be due soon but you do not have an easy way to finish it?
Tired of spending hours on time-consuming, labor intensive work?
Try UDQ Consulting Services -
Consulting Cost Starting from $25.00 - Your Small Investment Could Save Your
Hours Even Days of Labor-Intensive Repetitive Work!
Most of the time, you can receive solution to your problem within 24-72
hours.

www.geocities.com/UDQServices UDQServices@GMail.com

UDQ Consulting Services Believes that Every Problem Should Have an Easy &
Simple Solution
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
You first consulting work could be free if it is not too difficult - limited
time only!
Project Based Consulting Could be Either On-Site or Remote

No matter what problem you have, even you think it is impossible or has
nothing to do with Excel, try UDQ Consulting Services! You will get an
affordable and satisfactory solution.

= - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = -
UDQ Services has years of experience with MS Excel and VBA (Visual Basic for
Application) and has developed various kinds of utility programs, add-ins,
templates, spreadsheet applications, and database applications.

For MS Excel: File Password Protection, Sheets/Range Password Protection,
Conditional Formatting and Custom Formatting, Data Validation, List, Forms
and Controls, Array Functions, Database Functions, Value Lookup Functions,
Range Names, Customize Menu and Toolbar, Solver, Goal Seek, Scenario
Analysis, Statistic Analysis, Group and Outline, Subtotal, Filter and
Advanced Filter, Sort, Database Query and Web Query, Pivot Table and Pivot
Chart, XML, and Macros.

For Excel VBA: User-Defined Functions, Macros, Work with
Ranges/Charts/Sheets/Workbooks/Files/Directories, Forms and Controls for GUI
(Graphic User Interface), Customize Menu/Toolbars, API, External Database
Access via SQL, Extraction of Data from Internet, Extraction of Data from
External ASCII Files, Interact with Other Offices Applications (like Word,
Outlook), Class Module, etc.

VB6/Access/Others: UDQ Services also has experience with MS Access(Table,
Form, Query, Reports, Stored Procedure, Macro, VBA, Database Application),
VB6(ADO, DAO, Objects, API, ActiveX), Java, PHP, MySQL, Apache, HTML,C/C++,
SPSS, Matlab, etc.

= - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = - = -
List of Recent Projects:

P001: Mortgage Transaction Spreadsheet Database
Developed a spreadsheet database application, which includes a user-friendly
loan transaction entry interface, password protected administrative page to
allow add/remove loan officer and loan manager, monthly and annual report
for loan officers / managers, monthly state report, etc.

P002: Customer List Converted from Excel Table to Word
Developed an interface to convert a list of customers from an Excel table
into a new Word document, one customer per page, including user-specified
headers.

P003: Amortization Calculator
Developed an amortization calculator: based on user specified loan amount,
interest rate, and term, calculate the monthly payment, total payment, total
interest payment, payment schedule table, interest rate sensitivity charts,
etc.

P004: Account Data Extraction from Internet
Developed a utility program to extract housing pricing/tax payment
information from a government website based on a list of house address.

P005: Sales Invoice Database
Developed a spreadsheet database for sales invoicing. If user input an
existing customer, then the shipping/billing address, VAT number, Account
Number, etc. for that customer will be automatically filled into the
invoice, otherwise, the new customer entry will be automatically added to
the existing customer list database, automatically fill product information,
calculate the invoice, create monthly and annual report for sales.

P006: Data Extraction from Hundreds of ASCII Files
Developed a spreadsheet application with user-friendly interface for
importing data from hundreds for external ASCII files based on
user-specified keywords.

P007: Mass Email based on Criteria
Developed a mass email program using Spreadsheet. Based on criteria (replied
or not), send email (reminder) to each individual in the list with different
body contents, different subject, and different salutation lines - about
1000 emails in the list.

www.geocities.com/UDQServices UDQServices@GMail.com

Hello, I'm having an issue with Excel VBA.I went into the Data tab on the ribbon and under the "Get External Data" group I clicked "From Web." I logged into a website that requires a username and password and I selected a table that has data that updates regularly.

From here, I'm making a macro that will refresh using "ActiveWorkbook.RefreshAll" and copy the changed cells to another sheet. The idea is to create a historical log from the data. My issue is when I use "ActiveWorkbook.RefreshAll" it requires a username and password before continuing the macro. My question is how do you login when it requires the data?

If you can think of a better way of collecting the data, then please tell me.

I have created a spreadsheet that calculates how much it will cost to visit a certain city for a day.

One of the fields is a link to find the "per diems" from gsa.gov.

With the airfare links I create, I was able to insert a cell's input (dates, airports, etc) into a dynamic url and just have that work.

unfortunately, for the GSA website that I'm using as a reference: http://www.gsa.gov/portal/category/21287

You can't just insert a zip code or something like that into the URL, it always returns the same url, no matter what zip code you lookup.

Is there a way to have a link in excel automatically input the specified zip code into that field and have it bring you directly to the results?

and for a long shot - is there then a way to have it pull that data directly into your spreadsheet?

I know very little VBA, which is why I've been just using dynamic links in the version so far.

I have uploaded the spreadsheet - the target cells are d14 and d15 and the corresponding (similar links) are next to them, which just bring you to the website where you need to manually input the zipcode.

Any help would be HUGELY appreciated. Thanks!

Hi,
I want to retrieve data off a web page using VBA. I reused some code I found on this site - reuse is a WONDERFUL thing. I am able to get IE
(Internet Explorer) to start up, and navigate to the page I want
however... I am having a few problems

1) I really don't want IE visible - I have the visible property set to
false but it doesn't seem to work

2) I can't seem to tell - in code - when the page is really finished loading. As
my code runs, IE starts/opens up, and the computer focus is on IE -
while MS Excel is sitting there in the background and nothing happens
until I MANUALLY make MS Excel the program with the focus.

Based on the code.... I thought

I have Windows VISTA and Excel 2007 however, I will run this code on XP with Excel 2003.

My code is below

Any ideas/thoughts would be appreciated

Thanks
Vmusic

- - - - - - - - - - - - -

Dim myUrl As String
Dim strSearchString As String
Dim curDoc As Variant, curElement As Variant
Dim objIE As Object

'Set the vars
strSearchString = "<div class=""ds-list"">"

Set objIE = CreateObject("InternetExplorer.Application")
objIE.Visible = False ' This DOES NOT work for me

myUrl = "http://www.thefreedictionary.com/happy"

objIE.navigate myUrl

Do Until objIE.Busy
Application.Wait DateAdd("s", 1, Now)

Loop

MsgBox "It should NOT be busy now" & Chr(13) & "The current state
is: " & objIE.ReadyState
' If objIE.Busy = False Then
'Get the data from the web page used Document Object model
'End If

Hello,

I am pretty new to VBA still and am having trouble getting started on an application that I would like to create so any help would be greatly appreciated. The details of what I would like to accomplish is below.

I am trying to create a foreign exchange calculator in excel using VBA to create all the functions of the application. The website I have found that I believe will work in querying the data is http://www.imf.org/external/data.htm and more specifically, the exchange rate data section. I would like to program the application to allow the user to specify what date range they would like and this is where I need help. Is there anyway to program VBA to allow the user to select specific requirements such as length of time and which exchange rates they would like then pull in the data provided by the IMF website into excel? The purpose of the application is to give the user exchange rates with real time data given a certain currency.

Thank you for your help, again any advice on this will be greatly appreciated.

Hi Everyone

Can anyone offer a way to do the following:-

Get HTTP MetaData into Excel1. Take list of URLS
2. Enter to XLS Sheet
3. Have XLS (VBA Function) look up each URL
4. Pull down Meta Title & Meta Description of each URL
5. Display Result in tableI have found many resources which do some, but not all of these reqs. Also "Get External Data" is not sufficient as it only gathers on page data sources, and I am looking to import the Meta Data from a list web pages into Excel.

As a newbie to XLS - This is my first post to the forum so thanks in advance for your help.
There must be a XLS Champion out there who knows how to do this?

Cheers

XLS_GUNDA

Hello,

I was hoping someone could point me in the right direction, I have a spreadsheet i am trying to get going but in order to do so i need a macro or VBA to extract a lot of information from external sources to put into the spreadsheet, basically the spreadsheet has one column which has links in it "B2 - B16643" then I have another two columns (C & D) which i want to store the data in from the link, basically when the webpage loads for the link in the cell Bx, it has two or three lines on it, all just text, so i would like to only copy the first two lines, the first line would go into cell Cx where x is the row number, and the second line of the website would go into cell Dx again where x is the row number, and it would loop through the rows until it got to the end. The data is different for the different links on each line though, so i don't think i could just do a direct web import otherwise i would have given that a go.

Does anyone has a similar example where it grabs data from a website and puts the text into cells, the lines are directly at the top of the page, and they are the only writing on the page, no images or anything else.

If anyone could help it would be greatly appreciated.

Hi Guys,

I have searched the web but cant find a way to do what I am wanting to do.

I have a table refreshed from a MySQL database that I use to create a report. I can use this data quite well using formulas in the cells.
But in order for me to add more information to the report I use without making it cluttered I need to add some data as comments.
I have searched the web and found a number of posts regarding creating comments using vba which are very useful. The issue I have is getting the data to put in the comments.
I have created a loop that picks up some cell values that I want to use as criteria but I cant work out how to use these variables to search through the table and get the info I require to put in the comment. In a cell I would use offset/match or index/match to get the required cell value but how do I do this in VBA?

thanks for any advice.

Desgy

Hi

I am designing a webquery and have have managed to synthetically create the http:// urls that the web query data will be obtained from.
The 10 required URLs are in a worksheet that I want the vba web query macro to use when I run the macro.
Each web query result should be displayed in a newly created sheet.

The problem I face is using the URLs currently held in the worksheet as part of the vba macro.

The process will be as follows:
- run web query for each of the urls (10 seperate web addresses)
- create a new ws for each url web query result
- when web query is re-executed, delete the previous results pages and re-create new worksheets for new results

Web query vba code is as follows:


	VB:
	
 execute() 
    Dim WS As Worksheet 
    Set WS = Sheets.Add 
     
    With ActiveSheet.QueryTables.Add(Connection:= _ 
        "URL;http://finance.yahoo.com/q?s=msft", Destination:=Range("A2")) 
        .Name = "q?s=msft_1" 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = True 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .WebSelectionType = xlAllTables 
        .WebFormatting = xlWebFormattingNone 
        .WebPreFormattedTextToColumns = True 
        .WebConsecutiveDelimitersAsOne = True 
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False 
        .WebDisableRedirections = False 
        .Refresh BackgroundQuery:=False 
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Questions:
- On line 6 of the vba code, how would I replace the http address with the address of the urls in the worksheet? Eg:


	VB:
	
 ActiveSheet.QueryTables.Add(Connection:= _ 
    "URL;range("A1"), Destination:=Range("B1")) 
    .Name = "result_1" 

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

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.

	VB:
	
 ImportWordTables() 
     '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

I posted http://www.ozgrid.com/forum/showthread.php?t=143791 yesterday but received no responses so I have decided to approach the problem from a different angle. (please delete old post)

I would like to build a VBA query that will automatically submit a series of search terms (names) from column A, Sheet 1 in Excel to a website (http://www.consumerbeware.mgs.gov.on...arch/search.do) and copy the search results back to excel on Sheet 2.

On the website the type of search must first be selected from a drop down control labelled "Licence/Registration/Appointment Type" (or 'searchType"). The type of search to be selected is "Bailiff / Assistant Bailiff"

Of the 5 available search fields (File#,Name,Address,City,Postal), only Name will be used. The names stored in column A of the spreadsheet will submitted one after another and the results copied one after the next on Sheet 2. Note the number of results received can be 0 to 100 rows in length and I want to copy them all.

I believe the search results can be copied 'as is' in that they parse properly when pasting into excel. I suggest searching on a random first name to see what I mean. The data comes up in a nice selectable table but on a new page meaning you have to click on New Search or the back button to perform the next search eact time.

I tried modifying a number of other posts, the closest was from someone trying to search on a series of stock tickers but I could not get it to work. Here it is;

	VB:
	
 '
Dim QT As QueryTable 
Dim strConnectString 
Dim sTxt As String 
 
For m = 1 To 357 
     
    sTxt = Cells(m, "A").Value 
     
    strConnectString = "URL;http://uk.finance.yahoo.com/q/hp?s=" & sTxt & "&b=21&a=04&c=2009&e=21&d=05&f=2009&g=d" 
     ' On the Workspace worksheet, clear all existing query tables
    For Each QT In ActiveSheet.QueryTables 
        QT.Delete 
    Next QT 
     
    Columns("B:H").Select 
    Selection.ClearContents 
     'Range("B1").Select
     
     ' Define a new Web Query
    Set QT = ActiveSheet.QueryTables.Add(Connection:=strConnectString, Destination:=Range("B1")) 
    With QT 
        .Name = sTxt 
        .FieldNames = True 
        .RowNumbers = False 
        .FillAdjacentFormulas = False 
        .PreserveFormatting = True 
        .RefreshOnFileOpen = False 
        .BackgroundQuery = False 
        .RefreshStyle = xlInsertDeleteCells 
        .SavePassword = False 
        .SaveData = True 
        .AdjustColumnWidth = True 
        .RefreshPeriod = 0 
        .WebSelectionType = xlSpecifiedTables 
        .WebFormatting = xlWebFormattingNone 
        .WebTables = "21" 
        .WebPreFormattedTextToColumns = True 
        .WebConsecutiveDelimitersAsOne = True 
        .WebSingleBlockTextImport = False 
        .WebDisableDateRecognition = False 
        .WebDisableRedirections = False 
    End With 
     
     ' Refresh the Query
    QT.Refresh BackgroundQuery:=False 
     
     
    Range("j" & m).Select 
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=True 
    Range("B1").Select 
     
Next m 
End Sub 

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


Hi,
I'm in the early stages of learning VBA (much thanks to forums like this !) and I've searched high and low on the web and in my VBA book, but have yet to find a working solution to my problem:

I have a simple bar-chart on a worksheet named "Charts", and the data range it's pointing to is on a worksheet named "Model", all in the same Workbook. The data series is called "Strategy HPR", which I named manually using Select Data on the chart.
I have a macro that allows the user to select a date range on which to run a model, and then populates a range on the "Model" sheet with a column of dates and a column of output values from the model corresponding to each date. Therefore this output range size will vary in length, and I would like the chart to be able to adjust the "Series Values" and the "Horizontal (Category) Axis Labels", just as if I were to manually right-click the chart, Select Data, and adjust the ranges myself.

Here's an example of the data on the "Model" sheet (Columns A & B, rows 22-26):

------A------ B
22 3/17/2007 1%
23 4/21/2007 3%
24 5/19/2007 5%
25 6/16/2007 2%
26 7/21/2007 1%

Here's an example of my code (hopefully I'm doing the code tags correctly):

	VB:
	
 ChartRange1 = Sheets("Model").Range("B22", Range("B22").End(xlDown)) 
Set ChartRange2 = Sheets("Model").Range("A22", Range("A22").End(xlDown)) 
Sheets("Charts").ChartObjects("Chart 3").SeriesCollection("Strategy HPR").Values = ChartRange1 
Sheets("Charts").ChartObjects("Chart 3").SeriesCollection("Strategy HPR").XValues = ChartRange2 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I get "Run-time error '438': Object doesn't support this property or method" on the SeriesCollection lines above.
Also, the active sheet is "Model" when the macro is executed, which allows for the use of xlDown in the code.

I simply want the macro to have the chart adjust the x-axis labels and data to show only 5 data points if the date range is 5 months (set by the user), and 12 data points if the user chooses 12 months.
Any help would be greatly appreciated !


No luck finding an answer? You could always try Google.