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.

View 4 Replies


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







Copyrights 2005-15 www.BigResource.com, All rights reserved