Run A Number Of Individual Web Queries And Paste One Under Other In Worksheet

Sep 7, 2003

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

View 9 Replies


ADVERTISEMENT

Automate Multiple Web Queries In One Worksheet

Jul 13, 2008

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.

View 12 Replies View Related

Prioritize Queries And Worksheet Calculations

Jul 24, 2009

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.

View 2 Replies View Related

Transfer Data From Master Worksheet To Individual Worksheet

Oct 7, 2009

I receive a monthly download of individuals call-logs in one "Master File." For internal reasons, I need to separate every person's monthly call-log into individual worksheets. Unfortunately, the file is very large and copy/paste is very time consuming. I am operating on MS Excel 2007.

View 9 Replies View Related

Copy And Paste Value Macro / Save Individual File To Right Of Specific Tab

Mar 25, 2013

I have a current macro that saves each sheet as their own file. The first 10-15 sheets are not really necessary, so I typically delete them once the macro has run. I am looking to add two things to my current code:

1. Add a tab called Start. Make the macro look for 'Start' tab and then just save each sheet to the right of that as its own file.

2. Save each sheet in the macro as just a copy/paste value. They current have a lot of links and it makes each file close to 3MB. That makes it incredibily difficult to paste all in one email.

Here is the current code:

Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String
On Error GoTo ErrorHandler

[Code] ..........

View 1 Replies View Related

Paste Text Paragraph Into Excel And Give Each Individual Word Into Its Own Cell?

Sep 10, 2013

How do I paste a text paragraph into Excel and give each individual word into its own cell?

View 6 Replies View Related

Transfers (copy/paste) Information From One Master File To Several Other Individual Files

Apr 16, 2009

I have a macro that transfers (copy/paste) information from one master file to several other individual files. The macro works, but I would like
to enhance it by have it spit out errors in the process as follows: if cell A42 on file VP1.xls is not equal to Cell C35 on file Masterfile.xls then make cell E2 ERROR and fomat E2 bold-red, otherwise do nothing. The macro should continue regardless, I just want it to spot errors.
I think this is pretty simple is just a matter of inserting an IF formula in the macro which is something i don't know how to do yet.

View 6 Replies View Related

Generate A Master Worksheet From Individual Worksheets

Dec 8, 2009

I'm having trouble making a master worksheet that imports data from a set of worksheets that are in the same file directory.

Basically,
I have about 200 or so rate sheets and I need to get them all into one master worksheet so that way I can run different data and graphs without manually typing each one into the master worksheet.

I've not really new to excel; however I am new to VBA and I'm assuming that's what I need to use??

I've now uploaded all the correct files that I'm using.

Attached:
RC1272.xls
Master RCF Report.xls

I need to find a way to import the info RC1272 and others alike into the master file. In reality, I'll have about 150-200 "RC" to get moved over.

What stuff that I need to move from the individual RC files are highlighted in YELLOW in the RATESHEET tab

Here's a list of the items that I need moved over from RateSheet (from top to bottom and left to right) to the master file

Provider Name: Column B
Provider Number: Column D
OSS/IPC Resident days: Column F
Total Resident Days: Column G
Total Provider Beds: Column E
Total Allowance Days: Column H...........................

View 14 Replies View Related

Macro- Move Each Worksheet To Individual Workbook

May 10, 2007

I need a macro that will take each worksheet in my file & create a new workbook for each. The new workbook name will need to be the original worksheet name. The number of worksheets & the worksheet names will very each time I run this code.

View 9 Replies View Related

Delete Named Ranges From Individual Worksheet

Jun 16, 2008

I was after a bit of code to delete the range names on a particular worksheet I thought it couldn't be that hard but have only been able to find this in the archives, unfortunately I get an error when trying to execute it:

Sub Delete_My_Named_Ranges()
Dim n As Name
Dim Sht As String
' Put in name of sheet where the range is located
Sht = "Org Lookups"
For Each n In ThisWorkbook.Names
If n.RefersToRange.Worksheet.Name = Sht Then
n.Delete
End If
Next n
The error I'm getting is Run-Time error 1004, Application defined or object defined error.

View 9 Replies View Related

Save Selected Worksheet Into Individual File Vba

May 23, 2006

The idea was to save the the selected files specified in the array as individual files. For example, In the Workbook "Main", there are worksheets "Susan", "Mary" and "John". If i specify "Susan" and "Mary" in the array, it should output only these 2 individual files. However, I am only getting the file which is actively selcted in the main. Is there anything wrong with the for loop?

Sub SaveShtsAsBook()
Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
Dim ws As Worksheet
MyFilePath$ = ActiveWorkbook.Path & "" & _
Left(ThisWorkbook. Name, Len(ThisWorkbook.Name) - 4)
With Application
. ScreenUpdating = False
.DisplayAlerts = False
' End With
On Error Resume Next '<< a folder exists
MkDir MyFilePath '<< create a folder
For Each ws In Worksheets(Array("Mary", "Susan"))
SheetName = ActiveSheet.Name
Cells.Copy..............................

View 2 Replies View Related

Excel 2007 :: How To Lock Individual Cells In A Worksheet

Jul 23, 2014

How to Lock Individual Cells in a Worksheet excel 2007 .... i.e.

A2:A8

I want this selected area locked with password.

View 1 Replies View Related

Extension And Version: Send Each Worksheet To An Individual For Review

Jul 23, 2009

The other day, someone sent me a spreadsheet with multiple worksheets. I needed to be able to send each worksheet to an individual for review, and each recipient did not need to see everyone else's sheet. So I found a tip on-line that included the following VBA code to save the sheets to separate Excel files. Life was good until those who didn't have Office 2007 could not open their sheets, even though the original file had been saved with the Excel 97-2003 option. They had first received the message, "The file you are trying to open, "<filename>" is in a different format than specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?" When they said yes, all they saw was a scramble of machine code. I thought that maybe there was a special extension for Excel 97-2003 files, but when I looked it up in a book I have, it just said the extension was .xls, which is what the code used. Is there other formating that needed to be saved?

Sub Copy_Sheets_to_Separate_Workbooks()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = True Then
ws.Activate
ActiveSheet.Copy
With ActiveWorkbook
.SaveAs "C:Documents and Settingsruce.vanbibberMy DocumentsTITLE I - M A I NTitle I BudgetsAllocation Budgets - Schoolwide - FY 10" & _
InputBox("Please enter the Save As Name...", "Worksheet Save As") & ".xls"
.Close
End With
End If
Next ws
End Sub

View 5 Replies View Related

Counting Number Of Individual Values In Array?

Apr 23, 2012

I need a formula which ignores duplicates and uniques in an array, which returns the number of individual values.

A comma denotes a new cell;

1,1,2
3,3,4
5,6,6

In this case, the answer should be "6".

View 3 Replies View Related

Splitting Number And Storing It As Individual Digits

Apr 13, 2014

I am working on my project for VBA and I need to make a program for Large Factorials sch as 25!, which is too big to fit as a Long-integer. I basically have to find a way around this by storing the numbers as arrays F() and C(). Array F is for the multiplication of each cell of memory, and C is for the carries, which will later be added. I need to separate the hundreds, tens and units of each multiplication.

To Further explain, lets say I have 12!; to find this I would take the previously calculated 11! (by using a loop) and multiply 12 by each memory cell of the array F(). So it's kind of like multiplying by hand, you bring down the units and put the carries on top to be added later. I would store the carries as one or two memory locations over of the current I, or the current loop number. So the units of the mult. would be stored as F(I), the tens would be carried and stored as C(I+1), and the hundreds would also be carried, but this time two cells over, so C(I+2)

The main problem I have is finding the proper code to split the number into units, tens, and hundreds, and then assigning them to their appropriate memory cells within their respective arrays.

Here's what I have so far:

Code:
Dim F(1 To 30), C(1 To 30), N As Integer, Fact As Long, Length As Integer, space As Integer
Sub LargeFactorialCalc()
Open "F:project.txt" For Output As #1

F(1) = 1
N = InputBox("Enter the number you would like to take the factorial of: ")

[Code] .......

View 3 Replies View Related

Filtering Highest Revision Number For Individual Products

Apr 16, 2013

What's the easiest way of filtering a list of products to only show the highest revision for each product, preferably without using macros or VBA?

E.g. I only want to show the asterisked rows in the table below:

Product Rev
AAA 1
AAA 2
AAA 3*
BBB 5*
CCC 3
CCC 6*
DDD 1
DDD 4
DDD 7*

Potentially, other (random) data could also exist in the same row.

The example above is sorted alphanumerically but a solution for an unsorted table would be even more useful.

View 7 Replies View Related

Cell V7 To Tell Me If The Number In U7 Is Greater Than Or Equal To The Individual #'s In Cells U7-U40

May 4, 2009

I am using cell V7 to input the formula but I want cell V7 to tell me if the number in U7 is greater than or equal to the individual #'s in cells U7-U40 and I would like it to put a 1,2,3 in the cells of V7-V40 for the three highest numbers in order of largest to smallest if is this possible?

View 3 Replies View Related

VBA Code To Get Individual Values In A List Placed Into Individual Cells

Apr 7, 2014

My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...

Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..

When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of

Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x

I had

Code:
Combobox1.list = x

And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).

View 2 Replies View Related

Search Keyword In Worksheet / Copy And Paste Adjacent Cells To New Worksheet

Sep 24, 2012

I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.

[URL] ......

I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.

I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file

Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).

I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.

View 2 Replies View Related

Find Largest Invoice For Each Individual Identifying Code Number In The Table Without Using A Pivot Table

Sep 8, 2009

Data Table including-

List of Identifying Code Numbers for customer invoices

Multiple repetitions of individual Identifying Code Numbers in list

Various data in table range including Various Values of invoices from different dates for each repetion of Identifying Code Number.

- Wish to find largest invoice for each Individual Identifying Code Number in the table without using a pivot table.

i have tried combining Max and Large functions with Vlookups etc.

View 9 Replies View Related

To Copy And Paste Two Columns From A Worksheet To Another Worksheet Simultaneously

Dec 11, 2008

I would like to copy and paste two columns from a worksheet to another worksheet simultaneously. I would like to have a macro to do this function.

View 5 Replies View Related

Lookup Name On One Worksheet - Paste Content On Another Worksheet?

Jun 17, 2014

I have worksheet 1 and worksheet 2. I have a different list of names in column A in both worksheets. Some of the names in worksheet 1 are in worksheet 2, but only a partial list. Worksheet 2 names are not in any kind of order and the rows do not match up with worksheet 1. In worksheet 1, column B is filled with each person's email address. In worksheet 2, column B NEEDS to be filled with each person's email address.

Is there a way for me to take each name, sequentially, on worksheet 2 and find that same name in worksheet 1, then pull the email address from column B (beside the name) and copy it to that cell in column B of worksheet 2?

View 2 Replies View Related

How To Loop Through Each Worksheet And Copy Value To Paste In New Worksheet

Jan 31, 2014

I need to loop through worksheets in a workbook and copy every first cell value(A1) and then paste into a new worksheet.

I have tried various loops. some have copied first value for the first sheet and then pasted in the new sheet. while others have been not so good.

This is the code I have so far and this does not work at all.

Code:
Sub Check()
Dim ws As Worksheet
Dim lr As Long
Dim treg As Worksheet

[Code]......

View 1 Replies View Related

Paste Values From Another Worksheet (paste Special, Values) In A Cell Which Is Lookup Value I Get #N/A

Jun 28, 2009

I have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?

View 3 Replies View Related

Combine The IF And OR Queries

Jan 8, 2009

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

View 12 Replies View Related

Using Lookups & IF Queries Together?

Dec 28, 2012

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

View 4 Replies View Related

Precision Through Queries

Apr 28, 2009

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?

View 4 Replies View Related

VBA Inputbox To Sql Queries

Jul 31, 2007

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

View 9 Replies View Related

Web Queries In Modules

Aug 8, 2006

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

View 2 Replies View Related

Parameter Queries In Vba

Mar 30, 2007

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

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved