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
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.
Lets 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
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
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?
Is 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)
I 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?
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.
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.
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
I 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?
I want data to columns A, C and E, while I have important data in columns B and D. Should I make three queries? Should I make query and save the results to temporary place and then move all the results to those columns A, C and E?
Right now the copying happens like this:
If Not rsData.EOF Then rngTarget.CopyFromRecordset rsData Else MsgBox "No records returned.", vbCritical End If
So should I do 1) One query and move everything in the right places 2) Multiple queries 3) Something else
Does this depend a lot about how many results I expect? I'd say likely about 100, maybe less than 1500, never more than 10000
I have sql queries in spreadsheet column and which needs to run against Db2 database daily and then update the result back to spreadsheet. This is tedious process and could there be a way to create a a macro using VB which performs the following?
1. connect to db2 database
2. take the sql query one at a time from every row and then run against database
3. Obtain the result and then update it back to last column of the spreadsheet.
Below is an example of how I prefer to work with a database. Results are fast; even across the network and I understand the syntax perfectly.
Code: Sub DBPreferredWay() folder = ThisWorkbook.Path & "mydatabase.mdb" Set db = OpenDatabase(folder) strSQL = "SELECT * FROM transactions WHERE [SimilarItems] = " & myFocus Set tR = db.OpenRecordset(strSQL)
I'm trying to access a different database; a database being hosted on my SQL server. Because of security and what not; it looks like I'm needing to use adodb but I can't stand it and the slowness (minutes to receive queries. IS there a way for me to access my sql server database similarly to how I'm accessing MDB tables across the network? Below is what I've been trying with the adodb
Code: Sub SqltoACCPAC() Set conn = CreateObject("adodb.connection") Set rs = CreateObject("adodb.recordset") phrase1 = "DRIVER=SQL Server;DATABASE=" & "VNODAT;SERVER=192.168.0.91,1433": conn.Open phrase1, "SA", "password" strSQL = "SELECT * FROM ICITEM WHERE [SEGMENT1] >= 101 AND [SEGMENT1]
Using the Forms toolbar control, NOT ActiveX, in Excel 2002 (XP Home) to make on option button, how do I obtain more than just one option? I can draw the outline of the button and I can rename the option but I don't know how to get more than one. I'm totally green to VBA/macros/code/etc. so please keep it SUPER SIMPLE.
I'm hoping that by selecting one of 4 options, IF formulas elsewhere in my workbook (spread over multiple sheets) can be fine tuned.
For Instance: if option 1 is chosen (on sheet 1), =sheet2!(randbetween(3,24)). if option 2 is chosen, =sheet2!(randbetween(3,30)), etc.
I'll then record a macro that will take it to a Value using CopyPaste Special. Unless there is slick way of stopping the cells from rerandomizing when the sheet is recalculated.
I hope to use check boxes, to help me CONCATENATE stuff elsewhere in my spreadsheet.
For Instance: if checkbox 1 is checked (on sheet 1), =sheet2!(conc($D$11&" whatever is associated with the check box). if check box 2 is checked, =sheet2!(conc($D$11&" whatever is associated with the check box), etc.
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 was wondering if there was a way to Refresh Queries on a sheet by sheet basis. I have several sheets of queries, and I would like to refresh all of them except one or two.
The only way I know how to do this is to stick a line of code for each query into the script while leaving out the queries I don't want. I was hoping there was a more elegant way to do it. If not, no big deal. I'll just do it the old fashioned way.
I am writing a routine to retrieve multiple stock quotes by looping through a bunch of sheets and refreshing a bunch of querytables that access the web. My data source (Livecharts) is often clogged up and queries will fail or get lost in space so after initiating the queries I wait for a while using a waitable and then check to see if each query has completed or is still refreshing. If they are still refreshing I cancel them and refresh again. Or at least that was the plan. What I have discovered is that as long as the vba code is running the results from the queries do not come back into excel. As soon as the code is exited or I go into break mode in the debugger the queries complete.
Is there some way to get the queries to refresh during code execution? Here is a code snippet that shows what I am trying. I have defined a Class Module defining an "EventfulQTable" and it's associated methods and properties so I can have a QueryTable with events. The sub Wait uses the windows API SetWaiTable command to pause for a specified period of time. It includes a DoEvents command which I thought would allow the returning queries back into Excel but no such luck.
Sub Refresh_Queries() Dim SheetNum As Integer, ListSheet As Integer Dim eqtQT As New EventfulQTable Dim QueriesDone As Boolean
Can anyone explain how to name a chart? I've been accessing charts in my VBA scripts using chart names, e.g. "Chart 4". I've been determining the chart name by recording a macro and selecting the charts. But, is there a way to either:
1- change the name of a chart (as you would name a pivot table) 2- determine the name without having to select the chart in a recorded macro 3- reference multiple charts from the same worksheet without using their names