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'm looking to automate the process of repeating an excel worksheet (which is set up with various tables and formulas) for various countries.
I have a referenced list of countries set up in worksheet 1. So for example: in worksheet 2 I have a sheet set up for Albania - and I want to repeat this for another 100 countries. . Can I use the reference list of countries to auto-repeat worksheet 2 for worksheets 3 - 102 and auto-title the worksheet tabs? (E.g. Brazil, Canada, Chile..............)
I have a general question about the best way to perform multiple operations from excel and finally bring it together in one worksheet. I have currently a big worksheet (own application) which queries data from the database and then performs some calculations etc. The download takes some time and cannot be changed. Ideally I would like to separate the data download from the application and have two worksheets running at the same time. Worksheet Databaseinteraction.xls would do the queries (for example every 10 minutes), worksheet calculations.xls would do the rest.
When I start both sheets they should check that the other one is open (active). The user will interact only with the sheet calculations and do some things with it. To get the data into calculations.xls I would use an ADO call (this I have done multiple times). Databaseinteraction.xls would have to know when to start getting data from the database (for example using an ini file) and continuously query the database every 5 / 10 minutes (ontime). Calculations.xls would query the Databaseinteraction.xls also every 5 / 10 minutes. Both sheets should not freeze each other. If calculations.xls queries Databaseinteraction.xls no download from the database should be started until the first query has finished.
I'm trying to run a number of individual web queries and would like to paste them one under the other in worksheet2.
Sub ImportData()
Sheets("Sheet2").Select
With ActiveSheet.QueryTables.Add(Connection:="URL;" & Sheets("Sheet1").Range("D11"), Destination:=Sheets("Sheet2").Range("A1"))
.Name = "yahoo"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False.....................
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 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 developed an application (all written in Excel) that can do comparisons between up to 5 scenarios (A.... E, with A being the Master that the others are compared to. Any one can be designated “A”.
All scenarios are the same format, and all have an identical Transfer Worksheet, to enable the transfer of data from the others to A for comparison to A. So, for A the transfer worksheet becomes an Importer, wherein the user pastes-special – link into array, always B21:E35, one column at a time, for each of up to 4 scenarios B…E.
For the others, Transfer worksheet becomes an Exporter, available for the user to copy the data from each of scenarios B….E, always cells B41:B54....
Here is my scenario:
4 step process or more?
1)I have a text file (with delimiters) that I want to be copied over to an existing workbook (because of the header)
2)So now I have a temporary workbook that includes the header and the data from the text file. I want this temporary data to be transferred or copied to a sheet in another workbook(s), wipe out previous data and store the new one. Save the workbook(s) and close.
3)Close temporary file. Don't save.
4)I want this process to be automated -- run it daily everyday at say, 6:30 in the morning.
Each week our department receives a single Excel report that contains ~15,000 rows of data.
Column A of this report is populated with a store number.
Every time the report is received, we go through a manual process in which we have to break the report down into individual store numbers and then email the data to the branch managers for their particular store only (~40 emails).
I have attached two spreadsheets (1) Invoice Report and (2) Email List.
To clarify:
Send data for branch 124 to mike@xyz-company.com (column header + row 2 only)
Send data for branch 126 to susan@xyz-company.com (column header + rows 3-9 only)
Skip branch 140 as there is no data
Send data for branch 159 to tim@xyz-company.com and d.ortiz@xyz-company.com (column header + row 10 only)
And so on.
I want to do is to (1) create a report for each branch in the list (2) email the report to the specified recipients and (3) delete the created report.
I am using solver to get a list of values that make up mulitiple known values. I would like to make the code easier to apply instead of copying it and changing it for every cell. Auto Merged Post Until 24 Hrs Passes;
Sub Macro()
SolverReset
SolverOk SetCell:="$F$28", MaxMinVal:=3, ValueOf:="0.002", ByChange:= _
"$F$31:$F$37"
SolverAdd CellRef:="$F$31:$F$37", Relation:=1, FormulaText:="100%"
SolverAdd CellRef:="$F$31:$F$37", Relation:=3, FormulaText:="0"
SolverAdd CellRef:="$F$31:$F$37", Relation:=3, FormulaText:="$E$31:$E$37"
SolverSolve userFinish:=True
SolverReset
SolverOk SetCell:="$G$28", MaxMinVal:=3, ValueOf:="0.008", ByChange:= _
"$G$31:$G$37"............................
I am trying to automate text for a document. How can I combine Greek symbols and normal typing in one cell?
View 9 Replies View RelatedI am new to VBA coding. My boss recently handing me a task where I have to take data which is stored in thousands of fwd files and import it into spread sheets. This can be read with notepad as plain text. This data has about 35 lines of heading info i need to skip. The data i need transfered is stored in columns and each data row is tagged at the beginning with the letter D and comment lines begin with the letter C. The heading info has no such tags.The data is space delimited. Further, some of the columns of data need to be omitted in the spreadsheet. Each data.fwd file gets its own spreadsheet meaning each time i import data i start with a fresh template.xls to import into. The format of the spreadsheet is the same as the format the data is presented in the text file with ten columns, except the text has twelve and two of them need to be omitted.
One more thing, I need to be able to push a button and query the file to import.
My problem is automating a process that takes information from a single column with a range of D7:D77 in 22 spreadsheets and places this information in a summary spreadsheet as rows corresponding to the names of each spreadsheet. This is within the same workbook.
For example say spreadsheet A1 has a column from D7:D77 I would like this information in a summary spreadsheet with the row titled A1 and D1:D77 transposed. This would then be repeated for the remaining spreadsheets
I have a workbook that has a sheet for each month. Everytime something gets worked on in the shop, a line of information gets filled out in that months sheet. On a separate sheet in the workbook, I need to show what the top 5 things(according to how many entries there were for that item) that got worked on that month are. The info I need is all input into the same column, just different rows.
View 5 Replies View RelatedI have a worksheet of 5 columns. What I would like to do is subtotal an amount in column d, where the item code in column b are the same. Lets say there are 5 rows with code 8150 in column b, I want to add all these rows up BUT ALSO add up any rows which are 8150+1 (x+1) i.e. 8151. All transactions where the code in column B, x + (x+1) = 0. All true results should then be cut and pasted into a new worksheet (one worksheet for all transactions that are true).
e.g.
8150 1233
8150 12
8150 644
8151 -2084
8151 -10
8151 -205
Total of above amounts is zero, therefore cut and paste to new spreadsheet.
Now that I think about it, its actually quite complicated.
Trying to write an IF query looking for multiple returns that would equal the same result.
I have a series of number where the last digit would result in the same return if the digit were 1 or 6, 2 or 7, 3 or 8, etc.
1 or 6 = 1
2 or 7 = 2
I have been trying unsuccessfully to combine the IF and OR queries
I'm currently working on a side project for work that I believe will save many man hours as at the moment we're using paper and calculators!!! I'm scheduling buys. I've worked out formulas and formats to show how much we should buy and how they should be split (for example for UK purchases we buy x amount and split it 10% 90%). However, the dates of delivery are different. See below:
PORT L'poolPORT HKPORT Portsmouth
1.113-Aug-1206-Aug-1230-Jul-12
1.210-Sep-1203-Sep-1227-Aug-12
2.103-Sep-1227-Aug-1220-Aug-12
2.201-Oct-1224-Sep-1217-Sep-12
3.124-Sep-1217-Sep-1210-Sep-12
3.222-Oct-1215-Oct-1208-Oct-12
Campaign 1 first buy (1.1) I know that my product is sailing from port HK. Therefore I want it that in the 'delivery date' cell, it returns 6/aug/12 (that's the first lot of 10%, and underneath it returns 3/sep/12) which the remaining 90%...2.2).
However, both the campaign number AND port options are variable (using a data validation drop down list). In effect meaning if I choose campaign 2 instead of campaign one, it'll return the dates 27/aug and 24 sep....if I then change the port to Portsmouth, the dates will be 20 aug and 17 sep.
I've attached a quick view of the schedule I've created. The yellow cells are where I want the dates to be returned.
ScheduleEX.jpg
I have a program that uses a query to grab data from overseas. I have an issue where some precision is apparently lost and I was curious if there was a way around this.
As an example of what is going on I have a specific piece of data I am tracking. The value is 14.9 in the SQL database and it's datatype there is a real of length 4. After grabbing this item from the query I checked the record set and the value is still 14.9. After it reaches the worksheet though it is displayed as 14.89999962. The destination cell is currently formatted as General but I have tried Text as well. Both produce the same results. As this tool is very general I would not have it formatted as a number as I don't know what I am getting back exactly.
I know computers store real/single values inprecisely but I see it everywhere else as 14.9. I would think Excel would display what I am seeing. Is there anyway around this short of formatting the cell as number with 1 decimal place?
The report is a "template" that will be ran once a month every month and will look back at the previous month's data. What i've invisioned is writing a vb script to load on workbook open asking various questions via "inputbox()" method.
SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20070731 10:10:51.450'
SET @EndDate = '20070731 10:11:51.450'
SET ROWCOUNT 100
SET NOCOUNT OFF
SELECT EventTagName = EventHistory.TagName, Value
FROM EventHistory
INNER JOIN AnalogSnapshot ON EventHistory.EventLogKey = AnalogSnapshot.EventLogKey
INNER JOIN SnapshotTag ON SnapshotTag.SnapshotTagKey = AnalogSnapshot.SnapshotTagKey
WHERE SnapshotTag.TagName IN ('SysSpaceMain')
AND DateTime >= @StartDate
AND DateTime
I'm trying to produce a spreadsheet that pulls infomation from different web pages throughout the day. It needs to pull quite a large amount of information from different web pages hence I wanted to produce a subroutine which could be called with different variables for each web page, i.e. url, field, destination, etc.
To actually produce the query I recorded a macro and then used the code it produced. I then changed the URL to url, a string variable which holds the urls of the pages. The destion address was changed to destination, a range variable to store the destionation location, and the webTable was changed to table also a string variable to store the table number. This is shown below.
With ActiveSheet.QueryTables.Add(Connection:= _
url, Destination:= destination)
.Name = "q?s=%5EFTSE&m=L_137"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True.............................
I want know how to pass a parameter through a cell for date in the following url
http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=10
i would like to create reports and queries in excel just like in access is this possible or do you import a layout,i have a sheet that has data on i would like to be able to pick just one row or mutiple rows and print them off could i save them to a menu to use over and over again,would i use a userform to search for these or can it be done otherways ,have seen pivot tables but seem bit complicated
View 2 Replies View RelatedI use external data queries in excel. That's how I look up data in my access databases because I have so many access and SQL databases it's just eiser to be able to do quick queries on multiple databases at the same time in one spreadsheet. And, I can save the external data query as a tool to use in future queries. I have tons of tools and external data query is the foundation of most of them.
What bugs me is EDITING data.
If I want to do some light simple editing in an SQL database I am required to use a linked table in Access or SQL Studio Express Manager. Great programs, but I want to integrate!
Is there no way to make excel a database editor? I can retrieve data from any database or program with excel just fine, but is there a way to use excel to DIRECTLY EDIT the data in an external database with EXCEL like I can in access linked tables?