My question is about webqueries... specifically how to set up a query that will automatically fetch data on different pages (same data, but includes page 2, page 3, page 4, etc )
I've just figured out how to import a "new web query," but it seems like I won't be able to import 63 pages of it unless I do all of them by hand.
I'm new to macro and have tried copy from others who have posted before but it don't seem to lead to anywhere.
I'm trying to automated a column of web query (sheet1,column A), a batch of listing site with no tables. with the results in sheet2.
Looping part as well. Also, is it possible to just extract <title> from the page source.
Here's what I have so far.
PHP Code:Â
Sub Macro1()
'
'Â Macro1Â Macro
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;" & Sheets("Sheet1").Range("A2") _
        , Destination:=Range("B2"))
Â
[Code] ........
Tried changing the destination from
Destination:=Range("B2")) to Destination:=Range("Sheet2!$A$2"))
But keep getting error to change destination.
In short I have a yahoo query that loop through about fifty or so stock symbols. The query data is in column A to G starting with: Date, open,high,low ,close Adj. Now I have my Destination set up like so “Destination:= Range("A65536").End(xlUp).Offset(3))” for 3 row space between is retrieval
The problem is my: xlAscending
Selection.Sort Key1:=Range("A65536"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Ascending only convert the first query to Ascending the rest of the data remains Descending, how do I get all my query data to convert to Ascending using Date.
I can set up one query to take the parameters from say cell E1 and F1 and post the results of the query in cell A1.
The next query I want to take parameters from say cell E20 and F20 and post results in cell A20.
I need to run this several hundred times and am limited by the webite URL to 2 parameters.
I can't work out how to automaticaly change the URL via a macro so it adapts it with the changing parameters in my worksheet.
I am running 1,200 queries in succession in excel through a loop (visual basic). The problem is that, somewhere around loop 60, my computer grinds to a halt. I tried putting in a pause function and throwing in an autosave, to no avail. I think it has something to do with the memory, and somehow clearing it. Excel must be holding onto results from prior queries, and just runs out of memory after 60 or so.
View 4 Replies View RelatedLets say I have some web addresses in column A (Sheet1) and I want retrieve data from all those sites to another worksheet (Sheet2). Data from 1st site should be put to Sheet2!A1, from 2nd site to Sheet2!A51, from 3rd site to Sheet2!A101 etc.
There are some similarities with this thread: Dynamic Web Query From Cell Values, but I don't want the data to be on separate sheets and as my programming skills in Excel are rather limited, I failed to modify the solution given in there.
I'm trying to create a sheet in Excel 2007 that organises shipments, the file is normally pretty large so I want to create a macro which does a few things, so when printed it's easier to work with. First one is to make a gap when items change in Column B and Column F, I can use the below code to make the gaps for one column but not both without making a separate macro. Is there a way to make the gaps on changes in columns B & F on one macro? (I have an example nearer the end)
Dim Rng As Range
Dim x As Long
Set Rng = Range("F12:F" & Range("F65536").End(xlUp).Row)
For x = Rng.Rows.Count To 2 Step -1
If Rng.Cells(x, 1).Offset(-1, 0).Value Rng.Cells(x, 1).Value Then
Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down
Rng.Cells(x, 1).EntireRow.Insert Shift:=x1Down
End If
Next x
After the data is separated I wanted to insert totals of the weights when there is a spacing, and a counter which stops after each spacing and restarts when the next items start.
Here is an example of the data I have before the Macro with Columns.
Column B
Column C
Column F
Column I
Destination of order
booking ref
Size
weight of orders
[Code] ........
This is how I would like the date to look like after the macro.
Column B
Column C
Column F
Column I
Count
Destination of order
booking ref
Size
weight of orders
[Code] ..........
I have an excel sheet with a 'Home' and 'Data' page. I have imported data from an access query into the 'Data' page where I then push a button on the 'Home' page to run a macro on it. I have multiple queries in my database that I would like to be able to switch between in excel to run the macro on. Is this do-able without having to have multiple sheets?
View 1 Replies View RelatedIs there a really good tutorial for laying out multiple database queries in excel? Or even just for automating reports between Excel and Access? Here is my situation: Monthly, Quarterly & Annually I report on performance standards such as: duration, best practices, cost-benefit analysis, etc. The information is pretty standard and is pulled from Excel Reports. Access is used to filter this information by appropriate month, quarter, year, and/or data type. In access I calculate the performance averages, max and min. Then this is in linked back to Excel to update the charts and summaries. My concern is how much time I spend rebuilding this system every month (about 4 business days)
View 3 Replies View RelatedI am importing a text file that has a lot more than 256 columns. The data will be updated weekly with a different text file. I know how to get past the 256 column limit when importing a text file like this. This link explains the method I am using to import the text file. [url] Since I am importing onto several sheets I get the dialogue box asking me to choose a file to import from for every sheet when I use RefreshAll. The same file that is used by the first sheet will be used by the rest of the sheets. Each sheet just imports a different section of columns from the text file. I want to avoid choosing the same file 30 plus times (once for each sheet) when I click refresh all. Is there a code that would allow me to choose the file for the first sheet, and the other sheets would follow suite?
View 9 Replies View RelatedI have a command box that I type a number into, when you type this number and hit "OK" it searches that sheet in column B for that number, if it finds that number it deletes it, if not close. This works perfectly. I have 7 other sheets however, and I want it to search those sheets as well for the same number (still in column B) at the same time, if found delete the row, if not keep going. I have it working on Sheet2 (laser) but every time it fails on Sheet3 (punch). The thing that kills me is it's the same code. I have tried numerous things but have had no success.
Code:
Private Sub CommandButton1_Click()
Const strDelete As String = "TextBox1.Value"
Dim DeleteValue As String
Dim rng As Range
Dim rng2 As Range
[Code] .........
I have a workbook with multiple pages, two of which are labled "Summary" and "Questionnaire" On the Summary sheet, I would like to have a button or permanent dialouge box that asks the user if he wants to print the "Questionnaire" sheet. The button should have 2 options YES and NO. If they say Yes, change the contents of cell A2 on the Questionnaire page to "1". If they selct NO, make contents of A2 on the Questionnaire sheet to "0"
I have a macro that reads the contents of A2 on all sheets and prints if >o
I am working on a database with Names and Volunteer Hours worked over about five years.
What I have is one sheet with names and info, and the respective sheets for each year and different events they worked that year.
on each year sheet I have only the first and last name, which is all that I need for personal info. I have these names on the seet using the sheet1!A1 formula. However whenever I add someone new they will not update the new row on the other sheets, they will simply ignore it. How can I make it respect my row updates?
I recently undertook an excel based knowledge test and whilst I haven't been informed of the answers/scores yet I have been frustrated with my inability to find the answer to what seemed like a simple question on the test....
Q: Without using copy and paste, put A1:H11 and its associated formulas and formats onto all 7 worksheets within the workbook.
Bearing in mind all questions on the test generally used only a few keystrokes, I felt fairly stumped and with copy and paste excluded from the options I am convinced I answered incorrectly. But the worst of it is I left fairly contently from the test and thinking I would get home and satisfy myself with the answer outlining a simple oversight on my part, have been shocked that I've been unable able to find one anywhere, is anybody able to put me out of my misery here as it's beginning to really bug me!
I have a list that I need to print. It is only 2 columns, but 12 pages long. I would like to print 4 or 6 pages, side by side on the same sheet. I tried changing the print setting to 4 pages per page. But, when I looked at the print preview, it showed only 1 page per page.
View 11 Replies View RelatedI need setting up a conditional rule over 2 pages.
Page 1 displays a list of countries.
Page 2 pulls in the RSS feed from the BBC World News website.
I need to find a way to set a rule that will highlight the country on page 1 if it is mentioned anywhere in page 2.
I'am trying to create a document which automatically prints the next page on the same sheet.
I have created a spread sheet which prints the first 5 rows in the top of every page, but I can't figure out how to make excel print two A4's on one A3 side-by-side.
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 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 RelatedIn my excel, i have 3 worksheets. On the first worksheet, i have 2 pages since i have 2 sections side by side. I can't stack the sections due to different column widths. I am trying to see if there is a macro to stack the 2 pages on the first worksheet as one pdf page, and then have worksheets 2 and 3 as pages 2 and 3 of the pdf.
View 1 Replies View RelatedI need to print 2 pages on one sheet. Page 1 on top, page 2 below. I can get this to work on Portrait, but I need it printed Landscaped.
View 9 Replies View RelatedI have a list of approx 600 locations with codes that I want to populate into individual worksheets.
For example, the list (in excel) has the following
NEW YORK 12345 WW012
NEW JERSEY 23456 WW013
PENN 34567 WW014
(Each field is in it's own column)
The other worksheet is a template where the Site Name, codes would be placed is specific locations, one site per worksheet.
Is there a macro that can create one sheet per site with the proper information?
I have a workbook that has multiple sheets for patients (each named with SSN) with all such sheets having identical structure. I have the VBA code below that prints all sheets. Each sheet will have 7 pages when printed. Is it possible to amend the code so that it prints page 1 and page 3 from each sheets only?
VB:
Sub PrintSpecificSheets()
Dim WS As Worksheet, mySheets()
Redim mySheets(0)
[Code]....
When I tried using Autosum and just selecting the ranges like I do normally it gave me the #VALUE error. There's got to be a way to do this.
View 1 Replies View RelatedIs there a way to do a "find & replace" across multiple pages in an excel workbook?
I am trying to update 1 particular data-set, and need to do it across all my pages...just spent 30 minutes doing find & replace on each page.
I have data range which have three sections identical in shape and size.
How should i set the page setup so that each section print on a separate page.
is there any way in excel of making multiple pages with a drop down menu. Let say I choose 3 within the drop down, I'd like excel to make 3 pages (with the same settings of the first one) having a head/foot head pages. If you have a excel example(xls)
I just added a picture of what I actually want to do. I'd like to have the same design but adding pages between the top and bottom of the pages with a drop down or via an input box.. doesn't matter.
This code will hide row 2 across Sheet1, Sheet2 and Sheet3 while recording in the macro record mode but when played back after recording will only hide row 2 on Sheet1. How do I get this code to work?
Sub MultiplePageHideRows()
Rows("2:2").Select
Sheets( Array("Sheet1", "Sheet2", "Sheet3")).Select
Sheets("Sheet1").Activate
Selection.EntireRow.Hidden = True
Range("A1").Select
Sheets("Sheet1").Select
End Sub
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] .....
I've got an excel sheet that has a series of successive numbers down the side from 1-15 per sheet. I've done this by putting the cell formulas as previous row +1. (these are being used as unique reference numbers)
I want to be able to print this out multiple copies of this in one instance without having to manually adjust the first number multiple times then print the sheet. I also want it to be automatically saved as the most recent page printed, so that when it's printed the number continues on from the previously printed sheet.
I found a macro online which I have adjusted to increase a cell by 15 each time it is printed (to change the first number of the row). But I've realised that I'm printing the final page twice if I run the macro again. Is there any way to get round this? If I print 1 sheet there is also no adjustment in numbers.
I also haven't got onto the save part.
This is the macro I am currently using:
"Sub PrintSequence()
'
' PrintSequence Macro
' Macro recorded (altered) 8/16/2005 by Roy Wagner
[Code].....