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.
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...
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.
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 ?
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
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?
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.
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.
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.
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.
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.
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?
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 '
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
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....
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.
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.
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.
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.
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..
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.
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?
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
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.
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.
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