Macro Code To Extract From Web Pages

Jun 13, 2008

Can anybody post me some links to automating data extraction from Websites using VBA and IE. Not something I have ever bothered with before. Basically interested in using the controls and pulling tables.

ADVERTISEMENT

Macro Won't Execute Code To Put Focus On "print To X Pages Wide By X Pages Tall"

Feb 20, 2009

I'm trying to execute a macro and it won't put the focus (radio button) to select x pages wide by x pages tall in the Page Setup/Page/Scaling Area. I looked at the macro and can't find a setting in the code but yet the focus won't change. What can I do about this? In other words the radio button stays selected as "adjust to "" % of normal size. Here's the code...

View 2 Replies View Related

Code To Print Multiple Pages

Nov 8, 2008

I have a macro that cycles through several student ID numbers and prints a progress report for each one. It works great, but I would like to add an option to pick which printer to use at the beginning of the code. In other words, when I run the macro, the printer box opens and I can pick the printer. When I click okay to print, the macro continues.

View 4 Replies View Related

Excel 2010 :: VBA Code To Extract HTML Source Code Not Working On Google Sites

Dec 6, 2012

I have previously used the following code to successfully pull out IE webpage source code for string manipulation.

Its a crude example to demonstrate the principle:

Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public IE As Object
Sub Sample()
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

[Code] ......

However when I substitute in a Google websites address into the IE.Navigate command, the code runs to the "Source_Code = IE.document ...." line then flags up a Microsoft Visual Basic error. "Run-time error '438': Object doesn't support this property or method"

The webpage that I am trying to access is a confidential company site, so you won't be able to access it yourself, but starts with [URL] ......

The one thing that I have noticed about this website is the Privacy Report icon in the lower right status window (Picture of an eye with a restricted symbol in front). I don't know whether this is the cause of my problem, or purely an incidental observation.

Is there something peculiar with Google sites that means that the source code cannot be extracted in general, or is this an issue specific to my site ? Does the Privacy Report icon have any relevance, and if so how do I switch that off ?

Using :
MS Excel 2010
IE Explorer 8.0

View 1 Replies View Related

Macro To Format All Pages The Same

Sep 12, 2007

Im trying to get a macro that will set every sheet in the workbook i am usine, to match sheet1.

Sub LOOPY
Dim WS As Worksheet
'Loop through all worksheets
For Each WS In ActiveWorkbook.Worksheets
'Do this for all sheets except these sheets
If WS.Name = "Pcard Statement" Or WS.Name = "namedranges" Then
'Do Nothing
Else
'copy "pcard statement" page setups/print magins
Sheets(WS.Name).Range("A1").PasteSpecial Paste:=print margins?
End If
Next WS
Application.CutCopyMode = False
End Sub

View 9 Replies View Related

Multiple Calculations From Different Pages - Macro

Feb 3, 2014

I'm working on this excel tool that I would like to make as user-friendly as possible. It would act like a survey almost where the user would choose responses or input values for specific questions and then on other worksheets it would have the actual calculation and math.

I see that if one were to add a value onto worksheet 1 and which would be a variable that is essential to a calculation on worksheet 2 it does not run automatically.

If there is a combo box w text values that also needs to have a calculation on another page, can i just do an IF statement on worksheet 2 with combo boxes?

View 3 Replies View Related

Trying To Sort All Pages In Workbook With One Macro

Nov 30, 2011

Trying to create code that will sort data in mulitple worksheets in a workbook.

Here is the code I have... I am being shown on "object required error"

Dim sh As Worksheet

For Each sh In ActiveWorkbook.Worksheets
If IsError(Application.Match(sh.Name, _
Array("Drop-down lists"), 0)) Then

[Code] .........

View 9 Replies View Related

Pivot Table With Show Pages Macro?

Feb 22, 2013

I am trying to to create a macro that will complete a Pivot Table, then use the Show All pages function. The data has the same number of columns each time, but the rows will differ each and everytime. The content of the column which houses the data which eventually will be a page can differ as well. I continue to get an errro and below is highlighted when debug is selected.

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=xlPivotTableVersion12).CreatePivotTable TableDestination _
:="Sheet4!R3C1", TableName:="PivotTable2", DefaultVersion:= _
xlPivotTableVersion12

This is a very repititive pivot that is done many times during a month. I have tried just using the data as is then the pivot, and converting the data into a table, doesn't matter still get the error.

View 7 Replies View Related

Macro To Copy Cells Of Data From Many Pages Into One

Dec 19, 2013

Macro that can copy cells of data from about twenty five tabbed sheets in the workbook (subject to change) and compile them into one long worksheet (the first tab).

The cells to be copied will be fixed number of columns but variable number of rows. I only need copying of cells in one direction (from the tabbed pages to the compiled list), and I would like to keep the list automatically up to date or have a "refresh" button.

Here's an image to visually describe this:

View 7 Replies View Related

Listbox Change Macro Not Working On Multiple Pages?

Apr 8, 2014

I am running into a problem with a macro. I have the block of code below repeated about 30x in vba. When I went to create a new list box, ie: "List Box 2", in a new sheet it is not working.. The code is the exact same, but it is not working at all.

[Code].....

This is the second code for the second Sub

[Code] .....

View 2 Replies View Related

Designing A Macro To Copy Data From Web Pages Into Spreadsheet

May 17, 2006

I have a folder full of uniformed web pages ( name and design) and I was wondering if it's possible to create a macro that will open up each page, copy specific data from each page, and paste it into an Excel 2002 spreadsheet. Unfortunately, my skills in VBA are very limited at best and I'm not sure if this is doable or is it something I'll have to go into each page and copy the data.

View 3 Replies View Related

Macro To Paste Formulas As Values On Specific Pages

Jun 10, 2006

I have a macro that pastes formulas as values on specific pages (("CTY EME", "Int Center", " Total SW dist cost", "Int, pubs & oth", "Total". Is there a way to compress the macro ie with the sheet names?

Sheets("CTY EME").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

View 3 Replies View Related

Print Macro For Multiple Ranges And Graphs On One Worksheet To Separate Pages

Mar 26, 2014

There were 2 macros. One printed all of the defined ranges (40) on separate pages, and the other printed all the graphs ("charts" - 39) on separate pages. I tried to combine the VBA code to print each range and then the corresponding graph. Everything is still on separate pages, but it saved time because I didn't have to collate after printing. It seemed to work. Then I tried figure out if I could print them all to a specific tray of the printer as set in the workbook or as the printer default. Now both the combined macro and the original macro are giving errors.

Is the code I have correct to do what I am trying to do (print each range and then the graph all on separate pages)? Is there any way to put the output tray choice into the macro?

Sub Load_Data_Report()
'
' Load_Data_Report Macro (print all tables & graphs)
' Macro recorded 12/21/00 by xxx
'
' Keyboard Shortcut: Ctrl+j
'

[Code] ........

View 1 Replies View Related

VB Code To Extract Row Content

Nov 23, 2011

I have a workbook with 2x Worksheets "Data" and "Check"

On the worksheet Data I have a series of Values

Column A = Names
Column B = Cost
Column C = Dpt

I want to create a piece of VB code to extract from the list on the "Data" worksheet, only those lines where, there are 3x occurrences for a Name where Cost = zero

Eg

Data Worksheet Values

A1=JohnS
A2=SimonX
A3=JohnS
A4= JoeF
A5=JoeF
A6=JohnS

[Code]....

Result of ******* macro button with needed code would clear existing values in "Check" worksheet and show

A1=JohnS
B1=0
C1=A1
A2=SamK
B2=0
C2=X7

View 1 Replies View Related

Extract Source Code From .exe

Dec 14, 2006

Is it possible to decode an executable that was written in VBA? Not VBA for excel but just VBA.

View 6 Replies View Related

VBA Code For Extract Report From Tools?

May 1, 2014

VBA code to extract excel report from tools e.g. SAP or other tools.

View 3 Replies View Related

Code To Extract 4 Right Chars And Paste In Another Column

Oct 27, 2009

I am having problems with a macro I am writing and I need some guidance on where I am going wrong (I am still a noob at VBA).

I have pasted the code below (not the most efficient use of code I know).

I have a list of departments and codes from the Intranet at Work. Now this contains a Department Code, Department Description and within the Description is a number in brackets showing how high up the organisation hierarchy they are....

View 9 Replies View Related

Extract Words From Within Text Of HTML Code?

Nov 5, 2012

Many lines on my sheet have the following text in col B.

****** http-equiv="Content-Type" content="text/html; charset=UTF-8"> ****** name="generator" content="http://www.movabletype.org/"> Church Marketing Sucks: Evangelism & Outreach Archives

Is there a way to extract all the text or words between the and tags and put the extracted text into col D?

View 1 Replies View Related

VBA Code To Extract HTML Table Data To Worksheet

Apr 30, 2013

I am trying to extract the data values from the references Bundesbank page and get them into a worksheet so I can manipulate from there. What I have is below.

Code:
Sub Get_Data()

Dim IE As New InternetExplorer
IE.Visible = False

[Code] .........

View 9 Replies View Related

Macro To Copy Data From Sheet 1 To Multiple Pages On Sheet 2 In Correct Cells

Jan 15, 2009

I have attached a 97-2003 .xls file with data for multiple store locations on sheet 1, and the desired result on sheet 2. I am actually using excel 2007, but I dont think I need any special features that it provides.

I will try to explain the issue here without opening the attachment.

Here is an example of the Data on Sheet1

View 13 Replies View Related

Extract First Character Of Each Word In String To Create Code Letters

Nov 3, 2012

Junior Fit Softstyle T-Shirt
Antique Cherry Red

Junior Fit Softstyle T-Shirt
Antique Cherry Red

Softstyle T-Shirt
Antique Heliconia

Softstyle T-Shirt
Antique Heliconia

Softstyle T-Shirt
Antique Heliconia

This is column a and b. Looking at b I am looking for a formula that will pull the first capital letter out of each word like ACR to create color codes.

View 1 Replies View Related

Vba Code To Extract And Clean Data Based On A Set String Ending

Dec 5, 2013

I would like to extract competition naps from the competition entries and then remove the string ending "nap" from the data so it can be pasted to another sheet in it's 'clean' format.

on the example sheet column B will always be the first column and the range will extend to either G,H or I (always the max range as there are always 6 to 8 races)

i would like the code to search through these columns of data and find the cell ending in 'nap' then to return this in column i, once done remove 'nap' ending from both the source cell and column i.

once done to then go through all the rows and do similar

the end result would be all naps returned into cell j and all the 'nap' endings through the data range B to I (max) to be removed to leave the horses name only.

in book 3 the first row nap was sommersturm so i have shown the outcome i would like with the nap ending in I1 removed and the horse name returned in J1 again minus 'nap' ending.

the data will always be clean with no leading/trailing/excessive spaces and always be lower case too. sometimes a space is not in between horse name and nap but it's always last 3 characters i want removed still.

View 4 Replies View Related

Excel 2010 :: Code To Extract Unique List Of Dependents?

May 31, 2012

Excel 2010ABCDEFGH1EMPLeaderSamAsbertNoellaJackson2RosalineSam3LionelAsbert4KerryNoella5JohnnyNoella
6AliAsbert7RosalineSam8TimothyAsbert9TimothySam10ReginaldAsbert11PascualJackson12MichaelSam13ReginaldJackson
14MeganJackson15ShellySam16CandiceSam171819Sheet1

Here's my problem... Column A contains employee name & column B contains Team Leader name of the respective employee. Range D1:G1 should contain names of team leaders from column B. After that, depending on the name populated in D1, cells going downwards from cell D2 should contain name of employees of that team leader. Similar thing should be repeated for columns E, F, & G. If an employee shows up under two team leaders then it should be displayed under both lists.

Is there any formula/VBA code which can do this? Been after this for last 3-4 hours and now just lost in a maze of various Index Match combinations and array formulas..

View 9 Replies View Related

Extract Zip Code From Address Field - Return Blank If Not Found

Jul 17, 2013

I'm very new to excel. I need a formula to put in a column (I) that returns ONLY a five-digit zip code from the adjacent cell in column J, which is a full address. Nearly every entry is written differently, and many do not contain a zip code. I would like the cell to be blank if the cell in J contains no zip code .

I'm using the formula:
=MID(J5,MATCH(TRUE,ISNUMBER(-MID(SUBSTITUTE(J5,"","#"),ROW(INDIRECT("1:"&LEN(J5)-4)),5)),0),5)

This formula sometimes returns -**** format numbers, considering them to be negative and still five-digit. It also returns #N/A if no zip code is present, and I would like this to be blank. I'm sure I can nest the formula within an IFF, but have not been able to make it work myself.

View 4 Replies View Related

VBA Code To Get Formula Into Each Worksheet Then Loop To Extract All Worksheets Into Master Workbook

Aug 26, 2012

I wanted to know if there is any way possible to get vba to insert a formula in a cell to each worksheet in each workbook in a folder and then using loop to extract all the info from each worksheet of each workbook in same folder into a master workbook?

View 1 Replies View Related

VBA Code Error - Extract Data From Multiple Workbooks To Single Excel Spreadsheet

Mar 28, 2014

I have a folder with multiples excel sheets

Destination : C: Project CustomerExcel

I would like to extract and compile the information contained in these cells:
a5,c5,a6,c6,c7,a14,g14,e16,g16,e18,i18,a20,g20,h22,j22,h24,l24 all the sheets.

New sheet would contain the information of each sheet eg. Column A2= file name

And Row B2 to R2 or whatever will be the corresponding cells mentioned above for each cell. The code I have only brings back the file name but only a5 from the range but not the rest.

VB:
Sub MergeAllWorkbooks()
Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range

[Code] .....

View 5 Replies View Related

Macro To Extract Data

Oct 7, 2008

I would like to create a macro so that it will pull all the data from the worksheet "Data" and put it onto worksheet "Extracted Data". However I only require the rows which have Booking Office No SU17.

View 3 Replies View Related

Macro To Extract Data

May 24, 2014

Macro that extract information i need from column A into column B. My file contains comments regarding a sale made by a manager. And, the only information that pertains to me is the bags it will be using.

Below is a example of a comment (ROW 28 of the file):

PO 186
TRADER
SHIPPING WITH BROWN
240 X 20 # PANDA $ 9.90 EACH $ 49.50 CWT.
850 X 40 # PANDA $ 19.05 EACH $ 47.63 CWT.
US # 1 MAX. 4% BROKENS

The only information i need is 240 X 20 # PANDA and 850 X 40 # PANDA.

In the file, the Bags Tab, lists the names of all of the bags we current have in our inventory. I was thinking that a macro can run through column A of the comment tab and if it finds a match in the bag tab it would list it out in column B of the comments tab.

Here is my file. [URL]....

View 8 Replies View Related

Extract URLs Macro

Feb 4, 2009

I am looking to extract URL's from a excel sheet.

Actually,, I have some software, which I have opened in notepad,
and then copied the data into excel.

The data is a mess.

It has had loads of unwanted characters etc. I've managed to clean this all up now.
What I'm looking for if possible is to extract all URLs to another worksheet.

As an example, I have 1 sheet that has 1654 rows of data, that goes right to
column DK.

All URLs now have 1 space before it and 1 space after it,
For example:

(space)[url](space)
or
(space)[url](space)

If possible, I would like to be able to run a macro that would
look at this worksheet and find all the URL's, then return
them into a new spreadsheet in a list,,, and if possible
not returning duplicates,
(If it does find dups if a pop up could mention how many dups deleted)
if this is hard to do then no worries at all.

As a note,I'd spent a while looking for a macro to delete all unwanted characters,, found 1,,
so for info purposes, I post it below, seemed to run fine;
but if there are any other alteratives/suggestions, I'm all ears

Sub clean()

Dim vAddText(0 To 38) As String

vAddText(0) = Chr(1)
vAddText(1) = Chr(2)
vAddText(2) = Chr(3)
vAddText(3) = Chr(4)
vAddText(4) = Chr(5)
vAddText(5) = Chr(6)
vAddText(6) = Chr(7)
vAddText(7) = Chr(8)

View 9 Replies View Related

VBA Macro Code Needed To Run The Code Contained Within A Cell

Dec 17, 2008

I have a macro that, when run, needs to read the contents of cell B5, and run the code that it contains.

Cell B5, for example, would contain the text:
Range("B13").Formula = "SUM(D12:D14)"

I need a macro to "execute this code", as if it were in the macro itself.

I have assigned the above to a variable, but am not sure how to execute it.

EG.


Dim the_calc
the_calc = Range("B5").value

Now, how do I run the_calc ?

View 9 Replies View Related

Using A Macro To Extract And Rearrange Data?

Sep 6, 2009

I have a big collection of spreadsheets that are all laid out in the same fashion: from A2 down are the names of different individuals (one name per row). Column titles (from B1 right) are the names of unique qualifications that these individuals possess. In the cell where a row and column intersects is the date at which this qualification was awarded. If this doesnít make total sense, Iíve attached a very basic version of this to the post (Sheet 1) Ė although the spreadsheets Iím working with have hundreds of individuals and scores of qualifications, rather than just a couple!

This data needs exporting/rearranging into a format thatís recognisable by another piece of software that we use. In this format, the data is split into three columns: the personís name, a possessed qualification and the date it was awarded. This means that a single individualís name may have multiple rows, since a different qualification will be present in each row for that person. An example of this layout is shown on Sheet 2 of the attachment.

View 2 Replies View Related

Macro To Extract Numbers In Groups

May 8, 2013

I have a workbook called PETB. I would like to extract the following account number ranges in Col A to sheet 2 (the numbers are all 6 digits) for eg 273000

1) All numbers starting with 2730 and ending in a zero and 02 to be extracted together in one group for eg 273000, 273050, 273250, 273002, 273052 to be extracted below each each

2) All numbers starting with 2730 and ending in a 4 to be extracted together in one group

3) All numbers starting with 2730 and ending in a 6 to be extracted to together in one group

View 2 Replies View Related

Macro To Extract Specific Descriptions?

Aug 28, 2013

I have decriptions in Col B on sheet1. I would like a macro to do the following

1) Extract descriptions that contain the following text to sheet2 (including Cols A:B)

sale, sales, fact assist , discount, DIC & rebates, F & I (for eg sale pine desk, fact assist pine desk, discount retail desk, F & I sales)

2) Extract descriptions that contain the following text to sheet3 (including Cols A:B)

COS, O/allowance (for eg COS pine desk, O/allowance,

View 5 Replies View Related

VBA Macro To Extract All Unique Rows

Oct 28, 2013

I have the following table A

1
2

B
1
1

B
2
1

[Code] .....

I would like to extract all the unique rows from the above table so the outcome will be

A
1
2

B
1
1

[Code] .....

However I can't think of a simple macro to do it.

View 2 Replies View Related

Macro To Extract Tickers / Pivot Table

Oct 2, 2009

I need to manipulate some data in order to make it reasonable. I'm reading up on Macro's now in order to be able to do this stuff in the future, but I think macro's are a long term thing.

Actually before I go into that, is there any book one can recommend for macro's? I do have a friend's books, How to Do Everything with MS Office Excel 2003, MS Excel Bible 2003, and MS Office - Excel 2003 Formulas. Are these good or any other recommendations? In particular I would like some data to play around w/ so that when in the book they tel me to do something I actually have some data to manipulate. I imagine learning the basics of macro's will be easy enough, but as I understand it the real power of macro's comes from programming in VB, and I dont see how that can be covered in these books.

Now for the more specific questions -

My raw data looks like the attached excel file called Raw Data, I want it to end up as the other attached excel file called Result. How would I create a macro to read where the ticker begins and ends? Is there any way to do it w/o VB knowledge?

Raw Data:
[url]

Resulting Data:
[url]

More importantly, I want to be able to order the tickers by how many times they've been mentioned. For example in the second excel file called Result, Laura has been mentioned twice as often as anyone else. When I sort this list I want to sort by who's been mentioned the most. So I'd like to see 6 lines of her data, followed by the person mentioned the second most (if they had 5 lines, the next 5 should be them etc). For this one would pivot tables do the job, or do I need macro's for this as well?

View 13 Replies View Related

Macro To Extract Data And Place Into Template ...

Mar 5, 2008

I need to create a "purchasing programme sheet from extracted data in a different workbook.

In short I need to open the Allocation, Add stock qauntities to the the boxes with a quantity in it and if its a zero i need (Once the button has been pressed) the information to be extracted to the corresponding boxes in the Purchasing Programme Document: Description, Part Number and quantity.

View 11 Replies View Related

Macro To Extract Data And Place Into Template

Feb 23, 2009

able to press the create report button ive created, which will open a template report sheet....

View 13 Replies View Related

Macro To Extract Data From 3 Workbooks With Execptions

Feb 23, 2010

Iím not to sure this can be done but I will try and explain what I would like. I have a Master workbook simply called Master.xls (Price) and I need to extract data from 3 other workbooks, these workbooks are in a folder called INFO, the workbooks are called, Summary.xls,(Sheet1) Supplier.xls (Sheet1) and List.xls.(Sheet1). I have attached sample files of all the workbooks, the original Summary has about 2000 colums and the original List.xls has about 20000 columns.

The only workbook i would like to open would be the Master.xls, would it be possible to exract the data without opening the other workbooks? or at least to look like they are not opened. On the Master.xls

When the data button is clicked I would like the following info displayed, if possible. A3 info comes from looking at Summary.xls columns B & C and comparing it against List.xls columns A & B, when a match is found in either of the columns, the code in List.xls C column will be displayed in the Master.xls A3 cell
...........

View 4 Replies View Related

Macro To Find And Extract Specific Numbers?

Jul 29, 2013

I have 20 numbers from D3 to W3. Numbers range from 1 to 80. There are several rows with numbers.

I would like a macro to pic from each row specific numbers and copy them to EC3.

View 8 Replies View Related

Excel VBA / Macro To Extract Data From Website

Nov 15, 2013

Can VBA be used to extract a table from a website and output it on an excel sheet?

Basically we have to log in to a website and type data to our spreadsheet which is a bit tedious from the table on the website.

I'm guessing this can be done in some way but us having to enter username/password on website first to get to page we want will probably cause an issue would it?

View 1 Replies View Related

Writing Macro To Extract Data From Web Page

Mar 17, 2014

As part of a research project i need to extract as much data as possible from a webpage. The problem is to access each table i have to follow lots of links which I can't get to work automatically.

Its from a greyhound-data.com. So an example would be I want to extract all the racing stats for every dog that raced in swindon between 1st jan 2010-1st feb 2010. When i put it in the search engine I get 512 races in a table. I have to follow the link on name of race for each race.. Then I have to follow the link for each dog in the racers box, 6 for each race. Then when i'm on the dogs page theres a link saying on the top of the page saying a nember of races eg. 67races (number will vary for each dog). If I press that I get a list of all the racing stats for that dog. Its that table i need. But i also need the dogs name as a constant column. I need the table for each of the 6 dogs for each race out of the 512.. So in total 3072 tables.. It a lot of data and I have no idea how to get it?!

My biggest problem is I don't know how to say follow the various links. And I don't know how to loop multiple times - once for each of the races in the original list and then for the 6 dogs.

View 4 Replies View Related

ADVERTISEMENT