I am using Excel 2003 and I tried to copy stats from a web page to Excel using the Web Query function. I recorded the procedure using Record Macro function and the result I got is shown below. The URL is "http://somedomain.com/nba/pbb/gamestats.aspx?id=1139&year=2002". I need to change the 1139 value in the URL to its next increment like 1140, 1141, 1142 until it reaches 1202 (so 1139 to 1202, or "http://somedomain.com/nba/pbb/gamestats.aspx?id=1139&year=2002" to "http://somedomain.com/nba/pbb/gamestats.aspx?id=1202&year=2002". The year remains the same. Since I absolutely had no knowledge in excel vba, I manually changed the URL to extract the data. Is there a way I can automate this?
I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.
I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.
Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.
If the solution requires using code, I'm good with VB Code in Excel...is there VB for Excel code that could make this happen?
Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.
Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.
My query is this WEB 1 http://fc-web-phl1-101.phl1:8090/gp/...runReport.y=12
I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run. I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!
I'm trying to query a query in Access 2003, from Excel 2003.
The query in Access looks like: AccessQuery: [SELECT VBAFunction(field1) FROM Table]
The query in Excel looks like: ExcelQuery: [SELECT * FROM AccessQuery]
I use the following VBA code in Excel to excecute the query:
With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _ Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery) .RowNumbers = True .Refresh BackgroundQuery:=False iResultRowCount = .ResultRange.Rows.Count End With When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.
I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.
My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..
Is it possible to set parameters on a query but use a wildcard to return all instances? I have a query that I want to be able to set multiple parameters on but give the user the ability to select as many or as few parameters as they want to see. 2 of the parameters are number fields and 2 are text fields with no spaces.
I want to take the date value of a cell, add one day to it and paste it to another sheet's cell. For example if Sheets("x").range("i4").value is 12/12/1999. I want to add a day to it (13/12/1999) and then paste it to Sheets("y").range("d3").value
I have a sheet with the column for time the format should be h:mm:ss, the time begin with 0:00:00 and 10 sec periods 0:00:10 ...until 50sec again in the next count wud be 0:01:00 again 10sec periods.. How to write the Macro, that automatically increse the values of minute (mm)if the ss attains 60 and increment the hour(h) value if mm get 60.
I want to write formula in cells(many cells!) using concatenate. The desired output:
In Sheet2!D1 - i will write concatenate of Sheet1!C6 and Sheet1F6 in Sheet2!E1 - i will write concatenate of Sheet1!C7 and Sheet1F7 in Sheet2!F1 - i will write concatenate of Sheet1!C8 and Sheet1F
Copy and paste formula would not do so i created a macro stated below. The problem is a can not change the ActiveCell.Formula correction.
I have a table... One of my cells is called Capacitors and the number of the cell is 202600. This is the default value. How can i increment this value?
For example: I have capacitors in one cell with this value => 202600.
But I want to put capacitors in another cell and i want the value to be 202600 + 1 => 202601
Another cell with capacitors with 202600 + 2 => 202602 And so on...
I currently have a spreadsheet that parses a HL7 message string using "|" as a delimiter. The String that comes before the first "|" becomes the sheet name (Segment). The code executes on each line of the string (Each segment is parsed). The problem is that sometimes there are multiple segments with the same name. So instead of a new sheet being created, all segments are lumped into the same sheet with that name. What I am trying to do is have the code create a new sheet for each segment and if there it is already present, add sheet name with an incremented number.
So given the sample message The MSH, SCH, PID, PV1 etc sheets are fixed. The DoHL7Parsing routine takes the line and parses it out in the existing sheet. The problem I'm having is lines that are the same such as AIP get lumped into one sheet. What I would like to happen is have AIP1,AIP2,AIP3,AIP4,AIP5 and AIP6. Each with the line parsed out. These are parsed by segment not fields. I want to be able to recognize the existing sheets and create new sheets if multiple segments with the same name are being parsed.
I have a formula that I'd like to have increment by one, but I can't figure out how to do it.
Here's the formula: =IF(INDIRECT("'Supervisor'!B"&ROW()-4)="Y",E12-D12,IF(INDIRECT("'Supervisor'!B"&ROW()-4)="N","0",IF(INDIRECT('"Supervisor'!B&ROW()-4)="","0")))
I'd like to have the -4 in the equation increment to -5 when I paste it to the next row without having to fix each one of them.
I have a input worksheet and in this user have to manually enter the sequence no. The sequence no. heading is not always in one cell that can be in any between any cell from D14 to D21.
I need some code which start the sequence no. with 1 and everytime I click add button. It find the sequence no. in range between D14: D21 and add 1 in the relavant adjacent cell (which can be in from E14 to E21).
See Table below
Start withColumn DColumn ERow No. can be any from 14 to 21Sequence No.1Clicked AddDesiredColumn DColumn ERow No. can be any from 14 to 21Sequence No.2DesiredColumn DColumn ERow No. can be any from 14 to 21Sequence No.previous val +1 and so on
For i = 17 To FinalRow Step 14 'Select cells to examine based on For/Next loop Step... Cells(i, 4).Activate 'Check for "value"... If ActiveCell = Press Then
[Code]...
The "Cells(i, 4).Activate" statement steps my search criteria forward by the value of "i." The last thing that I want to do with this macro is to increment the value of A4, "Range ("A4").Select" and D6, "Range ("D6").Select" by the value of "i."
How do I increment a cell by 1 when data in another cell is changed. In other words...I am logging phone calls made. When I enter the date I made a call I want a cell labeled "times called" to increase by one.
I'm trying to increment a link to a file in my macro by 1 after each row. For example:
Row 1 "<a href="/ ai/grtcon/06-07-q2/gc1_e.html"> Row 2 "<a href="/ai/grtcon/06-07-q2/gc2_e.html"> Row 3 "<a href="/ai/grtcon/06-07-q2/gc3_e.html"> and so on...
I've indicated in the code in the first array where the increment should go (I'm not sure if it belongs in the array).
Option Base 1 ' sets first array element to 1, not 0 Sub makehtmlGrtCon1() 'Dimension all variables. Dim DestFile As String Dim FileNum As Integer Dim ColumnCount As Long Dim RowCount As Long Dim LastRow As Long Dim LastColumn As Long Dim OpnTags As Variant Dim ClsTags As Variant ' Array defines html tags to be used before each cell. OpnTags = Array("<tr class=""size-smaller"">" & vbCrLf & " <td><a href=""/ai/grtcon/06-07-q2/gc" & INCREMENTHERE & "_e.html"">", _ " <td>", _ ...................
In my Worksheet I have 3 Headings: Contractor Supplied/Installed; Principal Supplied/Contractor Installed; Principal Supplied/Installed. Each heading has 10 rows below. In the Rows, spread over merged cells (inherited remember), are 3 columns:Item description; No. off & Remarks. When I print this I get an A4 sheet with 30 Rows and perhaps only 2 items in one of the Headings.
What I want to do, is to have initially 2 empty Rows per Heading shown. Then after entering data in Column A, Row 1, another Row automatically inserts below the empty Row.
I envisage there is always 1 empty Row. If that is possible I can minimize the use of paper and have 6 Headings on 1 sheet.
How to increment a formula with an If statement that transforms 2 values in time format and then as long as these two values keep beeing added in the cells the formula keeps active in the column.
I tried to apply the code for all the but this makes my file huge like 20MB!
You can check this on the file, go the "records" sheet, add the time in the "shift start" and "shift end" fields, then the difference value goes to "hours" in the sheet.
I'm writing some VBA code for a Macro to run that will save my file in a directory using the file name from a cell. I want to be able to increment the save file name if the same file name already exisits
i.e.
If no file then save as FileName If exists then FileNameVer2 If both exists then FileNameVer3
And so fourth (maximum iterations will be about 10)
I'm new at VBA but written some code to save
VB: Sub SaveAs() Dim FName As String Dim FPath As String
FPath = "S:TestTest Directory" FName = Sheets("Parameters").Range("B9").Text ThisWorkbook.SaveAs FileName:=FPath & "" & FName End Sub
At the moment I have concatenated four cells to produce the file name in B9 but if there was a way to use variables that would be awesome too!
The code runs when I press a macro button saving save, but at moment it says "file alerady exists , overwrite" when I put no, it goes to a debug.
I have a spreadsheet with 2100 rows. I am trying to fill one column with a numbering system that fills the same number in for 21 cells and then increases by one. For example:
I'm trying to copy data from sheet1 to sheet2, once it's in sheet2 increment it so I can calculate a total/count/avg of the data. I have some code that isn't compiling and I think it has to deal with the range of where it's being pasted. Also - I'm confused on how to make the data increment on the other worksheet. I have everything else figured out. I've attached copies of my worksheet for visual aid.
Step 1: Enter Code in highlighted area - Select Submit Data Button Step 2: Fills incremented position on worksheet 2
See Code Below:
Looks like it's not reading the "Model" correctly. I keep getting the Else message "Wrong Model Entered...."
I changed the ModR to: ModR = Worksheets(2).Range("A:A").Find(Worksheets(1).Range("B3").Value).Row
I have two columns C & D. In col C (C10) a time is entered (either 13.45 or 0:33.22 format). If column D (D10) is blank then the time in Col C stays the same (13.45 or 0:33.22). However if Col D has a M in the cell (D10) I want to add 0.24 to the time in Col C (C10) eg 13.69 or 0:33.46. I have tried various formula combinations but keep getting circular reference errors.
