Save Old Data When Web Query Refreshes
Jun 27, 2009When the data is refreshed, how can i keep the previous data and use it in a graph that automatically updates?
View 12 Replies
ADVERTISEMENT
Web Query Refreshes Not Updating
Dec 31, 2008Written in Excel 2002, I have a web query in sheet 1 that automatically updates every minute. In sheet 2 I have some VLOOKUPs that retrieves the new data from sheet 1. I sent this worksheet to my brother, who has Excel 2007. Sheet 1 does update every minute, but in order for Sheet 2 to update he has to either click refresh or F9. My version works without any intervention.
View 9 Replies View RelatedHiding Runtime Errors: Refreshes A Specific Data Range That Imports Data From A Text File
Nov 25, 2009I have a macro that first refreshes a specific data range that imports data from a text file as such:
View 2 Replies View RelatedStopping Cells From Automatically Refreshing After External Data Refreshes
Jan 30, 2010I have 2 Excel documents.The first document is named "data" and it extracts data from off the Internet every 24 hours. The second document, named "database", is for storing this data. Every 24 hours, I run a macro which imports the data from "data" into a new row in "database", along with the date the macro was run.
My problem is this: Whenever I refresh the external data in "data", my "database" document also refreshes itself instantaneously. My question is this: Is there a way to "cut the connection" between the two documents so that, when "data" is refreshed, the data in "database" DOES NOT also refresh itself automatically?
Disable Save Query Definition Option Vba
Jan 25, 2007I want to be able to disable the save query definition option for a worksheet using VBA. Right now I have to right click a cell and then go to Data Range Properties and then uncheck the save query definition box. If there is a way to automate this that would be great.
View 3 Replies View RelatedCreate An Conection With MS QUERY To A Csv And Query Data
Sep 6, 2008I 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!
(Save As) Variable File Format Routine Query
May 31, 2012The following code stops at the red line with "Method 'SaveAs' of object '_Workbook' failed.
FF="xlCSV". It works fine if I replace
Code:
FileFormat:=FF
with
Code:
FileFormat:=xlCSV
Code:
Sub SaveIt(FileNm, FF)
Dim FSO As Object, a
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FileExists(FileNm) Then FSO.DeleteFile FileNm
If FSO.FileExists(FileNm) Then
[code]....
Pivot Table Doesn't Refreshes?
Jan 20, 2013I created a pivot table. But when I enter a new value in a cell it doesn't appear in the pivot table. I have to create a new pivot table and than I can see that new data is entered in a cell. For instance, my pivot table holds dates as ROW LABELS and COUNT OF these. And when I enter a new date, that date is not shown in the ROW LABELS until I create a new pivot table.
View 4 Replies View RelatedCreate Addin That When Clicked Refreshes All Pivot Tables?
Mar 12, 2012I am trying to create an addin that when clicked refreshes all pivot tables and queries insteading of using the right click refresh or refreshall option button. This is part of a larger project which requires the addin -vs- the built in functionality. Anyways...the following script if I put it in a button and run it...it works perfect....if saved and run as an addin it blows past the refresh code and does not refresh anything, but will cycle through each worksheet.
I also tried thisworkbook.refreshall and activeworkbook.refreshall and neither worked.
Code:
Dim wks As Worksheet
Dim p As PivotTable
Dim qt As QueryTable
[Code]...
Excel 2010 :: Access Data Connection Query - Missing Data All Of Sudden
Oct 19, 2012I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.
btw...I am running Office 2010
Data Connection From Web / Web Query Returns Code Instead Of Selected Data Table
May 31, 2014A web query I am trying to use to bring an online data table into my worksheet is broken. Now, instead of returning the data table nicely into my worksheet, it imports the code of the webpage, instead, and turns my worksheet into a mess.
The query used to work but there was recently an "upgrade" to the program that populates the web table and the query no longer works and just returns the code for the page, instead. I can see the html code for the table in all of the code it returns but I really need the table to import cleanly into excel.
I can't talk to the people who changed the web program.
External Web Data Query Fails To Insert Table Data?
Jul 9, 2014An external data web query points to a web site that offers a foreign exchange rate calculator. In my browser, I selected the specific currency pair I need and used the resulting url in the web query. The "New Web Query" pane resolves the url correctly and allows me to select the table data I want (the little yellow arrow turns to a green check mark). However, when I hit "Import" and select the target cell, the only data returned is the parameters from the url and an error msg (This web query returned no data...). How do I get the query to pass the parameters to the website correctly?
View 3 Replies View RelatedPass Parameter From Excel Through MS Query To MS Access Query
Nov 26, 2012I 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.
Query Parameters Which Takes The Date From The Cell Into The Query
Mar 29, 2007Need 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
Selection=15
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
Match Numeric Data With Query Text Data
Oct 2, 2007I have some code that goes through some data that is imported from a database via a query.
This has been working for a few years without a problem.
I lookup and employee number on my sheet to the employee number in the database table.
this is my code that checks the employee number.
Set rs = Worksheets("Employee")
If rs. Cells(r, 2) = cells(1,1) Then 'if employee number matches
......
The problem is that now the database application has adapted an alpha option (it used to be numeric only). In order for my code to work I have to change the employee number on my sheet by putting a ' in front of it.
Example if the number was 127 I need to enter '127 in the cells.
I have a few thousand in my data.
Can I add some code to my macro so I can still enter just the number in the cells?
Edit Web Query - Get Data From Web
May 10, 2014i have a worksheet that gets data from web - its automated, but the website goes through a tunnel - with security - requires username and password
the Query Runs ok and smooth but i have to login manually by right clicking on a table where the query is and selecting "Edit Query" so i can login, excel vba doesnt save passwords for that part...
one way i find it possible to automate that part too would be to use maybe like the sendkeys statement to pop up the context menu from right clicking the mouse button, or like the options button on the keyboard.
I found this one:
[Code] .....
It works but the context menu comes exactly from where the current mouse position is..
I needed it to do the right click on a specific range in the worksheet (where the web query is)..
Pulling Data From Web Without Web Query
Mar 5, 2010information from a website that does not allow web queries (or at least from Excel 2003).
I have to pull the latest data every Wednesday from this webpage: [url]
To make things even more complicated the name of the page changes every week as well (corresponding to the date).
Data Validation Drop Down Query
Jan 12, 2010Would anyone know if it is possible to extend the size of the list presented when using the data validation drop down? At present, it seems to default to 8 choices (even though there is a scroll bar) but I was wondering could the list Automatically display, say 20 choices?
View 3 Replies View RelatedWeb Query Data Into One Single Cell
Dec 30, 2008I tried to import web data from a textarea (look at example2.jpg) into excel and it always end up squeezing all the information into one single cell(spreadsheet.jpg).
I am trying to find a way to edit the imported data which is similiar to paste special or extract the information from the cell into a table, but I seem to waste a lot of time on this and going no where.
Returning Data From Microsoft Query
Sep 7, 2009I have a query which is giving me some trouble when returning the data from ms query. The query was written in mysql query browser and returns 2 columns of data - a date and a number. MS Query correctly processes the query but when the data is returned to excel only the second column is returned. Here is the query:
SELECT
(SELECT
max(l.the_date)
FROM
nc_view_date_functions AS l
WHERE
l.week_of_year = d.week_of_year
AND l.yyyy = d.yyyy
) AS week_end_date,
count(r.consent_id) AS weekly_count
FROM
rg_resource_consents AS r
INNER JOIN nc_view_date_functions AS d
ON r.application_date = d.the_date
GROUP BY
d.yyyy, d.week_of_year;
Data 'is Not Number' Character Query
Oct 5, 2009They have a list of data numbers which are yy/#####/@@@@@ (2digit year/casenumber/alphanumeric code). Where some people have typed in the code wrong (6 or more digits in case number) we want to know is there a way of doing this.
Ideally I want something that says "If 9th character is a slash then yes, if not no" or "if 9th character is a number then yes, if not, no".
Example of data is (where we want third option highlighted as erroneous (6 not 5 numbers in middle)
09/56487/KFJT
09/42105/PLOUR3
09/002145/PLIFD
09/02145/ASDF
Find Data That Matches Query
Nov 9, 2008I have a spreadsheet with 3 columns: Name, Number and percentage. I need a formula to return the name that has the percentage and the 2nd highest percentage. the spreadsheet has 5 rows, not including the columns labels.
View 2 Replies View RelatedIdentifying Numeric Data With MS Query
Dec 6, 2006I am trying to slim down my database results in Excel via MS Query by searching for Part ID's that are numeric (we have parts that also contain letters....I want to weed those out).
In all my searching on the web, I thought the ISNUMERIC() function should be the function for this, but I keep getting an ORA-00904::"ISNUMERIC":invalid identifier....
Is this function supposed to work or is there another function that will do this
This is my SQL statement so far, which works to get parts that are 6 characters long only:
SELECT PART.ID
FROM SYSADM.PART PART
WHERE (LENGTH(PART.ID)=6)
When I change it to this to get parts that are numeric, it gives the error above:
SELECT PART.ID
FROM SYSADM.PART PART
WHERE (LENGTH(PART.ID)=6) AND (ISNUMERIC(PART.ID)=1)
Extracting Data From Access With SQL Query
May 10, 2009I have been trying to pass a query to fetch data from Access database.
The query is:
sSQL = "Select Client+, Entry-ID, Type of Contact, Manual Creation, Date Created, Transfer-date, Respond SLA FROM BASE DATA"
The problem I am facing is in:
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, Options:=adCmdText
The error I am getting is:
Run-Time error '-2147........'
Method 'Open' of Object '_Recordset' failed...
Copy Data From Web Query To A Table
Jun 26, 2006I use web query to get some range of data. This data is refresing and changing on some time (i set query refresh on 1 minute). So, i'd like to fetch this data and make the table with all data (new data on eg. every 1 minute). How to do this?
I use Excel 2003.
Data Source For Msquery Query
Nov 16, 2006I have inherited a file that calls an Access database via Msquery. I can see the query that is being used with:
Data | Import External Data | Edit Query | Query Wizard
How can I determine the Access database that the query is using for its data source?
Pull Data By Using New Webbased Query
Mar 4, 2007I am trying to pull data using New Webbased query. But when I try pasting the link it is promping me that the string is very large.
View 3 Replies View RelatedDatabase Query Does Not Return Any Data
Mar 15, 2007I am using the attached code however when my database query does not return any data I dont want the Dim function to perform.
Sub macro3()
Worksheets("Data 1"). Range("I3:BU65536").ClearContents
'declare a variable to hold date
Dim strDate As String
strDate = Format(CDate(Range("startDate").Value), "yyyy-mm-dd") & " " & FormatDateTime(CDate(Range("startDate").Value), vbLongTime)
Sheets("Data 1").Select
Range("A1").Select
With Selection.QueryTable
.Connection = _...........................
Query An Access Query With VBA Function
Jul 8, 2008I'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.
Edit Query Using Query Wizard
Apr 25, 2006I 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..