Assigning Cell Value In Web Query Connection?

Jun 15, 2014

I have a macro which retrieves data from web.It works fine.I just don't know how to assign a cell value in web address.i have attached the code here. i just want to assign activesheet.(range"c2").value for "q?s".

View 1 Replies


VBA Set ODBC Connection String For Query

Jul 25, 2008

My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?

The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.

Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).

View 9 Replies View Related

Running Access Query In Excel And Connection

Aug 5, 2003

I am not sure how to setup the initial connection to the datbase from excel and was wondering what would be the best way to go about things. Is it possible to run the query stored in the database and pass it a value or would I be better off just coding it into a sqlstring within the macro.

Here is the query that is in the database...when run it asks for the "Status?" variable.

SELECT ordernumber, mobilenumber
FROM bookings
WHERE status = [status?];

So yes, really simple query but yeah just want to know how to pass it that variable from excel or can I just set it up in the actual macro to maybe read the status from a cell?

View 9 Replies View Related

Working Access Query Fails When Using Excel Data Connection

Sep 6, 2012

I've about 10 pivot tables in Excel that are populated via an External Query link to Access. In each case I have pre-written views with all the columns I need so that the query can just pick them up. Nine of the ten queries work perfectly, but one doesn't - reporting "Too Few Parameters - Expected 1" when I try and click on the "Return Data to Excel" in the import wizard.

I can see the column headers in Excel to select; so I'm assuming that it can parse the SQL but no results are being returned. Just to confirm though - results are returned (>8000 of them) in Access.

SQL code below:

SELECT z.pathdurationhours AS PathHours, Count(z.pathdurationhours) AS Paths,
Count(z.pathdurationhours)/(select count(*) from (select [activity date],
[conversion ID], count(*) from tbl_dfa_p2cdata group by [activity date], [conversion ID]) ) AS PCT
FROM (SELECT a.[activity date], a.[conversion ID], val( max(a.[interaction time])-min(a.[interaction time]) )
*24 AS PathDurationHours FROM tbl_dfa_P2CData AS a WHERE a.[interaction time]
"" GROUP BY a.[activity date], a.[conversion ID]) AS z
GROUP BY z.pathdurationhours;

View 2 Replies View Related

Excel 2007 :: ODBC Connection Query Shows No Result

Mar 25, 2013

Why this sql query doesn't return values? My excel version is 2007.

if object_id( 'TEMPDB..#TMPDOC') is not null
drop table #TMPDOC

create table #TMPDOC (Code varchar(5) NOT NULL)

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

View 6 Replies View Related

Excel 2007 :: Workbook Use One Data Connection With Different SQL Query For Each Worksheet?

Oct 13, 2013

I'm trying to build a workbook (2007) with multiple worksheets where each worksheet corresponds to a different query/dataset pulling data from the one database.

I would have expected that you could define the one data connection object and then specify the SQL that each worksheet table will invoke. The apparent structure of having the query coupled to the connection object is confounding me. I'm therefore trying to set up a separate connection for each of the worksheets and Excel is getting tied up in knots.

View 2 Replies View Related

Excel 2010 :: Assigning Name To Range Received By ODBC Query

Apr 29, 2014

I have been working on some ODBC queries in Excel 2010.

First, I recorded them using Excel's Record Macro function, to get an idea of what sort of source data I would need. Then, I rewrote them into something a little more intelligible.

Pulling out all the sensitive info, I want to know how to add the .ListObject.DisplayName property back onto the results of the query. I tried doing it intuitively, but it didn't work. It was in there when I recorded the macro, but I can't seem to figure out where to put it back into the re-written code. Other parts of the code depend on the results of the query being a 'named field'.

[Code] .....

Also, where would I find out what all that stuff in Cnnect means? DBA, APA, EXC, FEN, etc are all just assignments, and I might like to change some to make this run a bit quicker, if I knew what they meant.

View 6 Replies View Related

Excel 2010 :: Access Data Connection Query - Missing Data All Of Sudden

Oct 19, 2012

I 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

View 2 Replies View Related

Data Connection From Web / Web Query Returns Code Instead Of Selected Data Table

May 31, 2014

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

View 3 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

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


View 2 Replies View Related

ADO ADODB.Connection: Preserve Cell Formatting

Oct 4, 2006

With ADO (ADODB.Connection), is there a way to preserve the cell formatting on the worksheet that the recordset data is copied to? Right now, if I format a Cell's font and font-size, then run the Macro to refresh the data, the formatting is gone and it's back to default formatting.

View 2 Replies View Related

Assigning The Range Of A Cell A Value

May 5, 2008

I have a number of cells which are connected to various formulas. EX. A1 = 7. Well I want a formula in A2 which can look at A1 and if A1 = 5-6, then A2 = 1, if A1 = 7-8, then A2 =2, if A1 = 9-10, then A2 = 3, if A1 = 11-12, then A2 = 4. And so on.

View 11 Replies View Related

Assigning Macro To A Cell

Oct 7, 2011

How do I assign a macro to a cell. Tried right clicking but "assign macro" but not there...

View 1 Replies View Related

Assigning A Variable To An Unknown Cell

Dec 27, 2006

I'm attempting to create a macro that will look at the total in column (K) and send an e-mail to two different addresses, depending on the amount. If the amount is over $10,000 then one address ( if under, then the other (

My main problem is that I never know what cell the total is going to be in.

I currently have all quotes going back to the person that sent in the request, no matter what the amount. Their e-mail is in the sheet.

So what I think I need to do, is find the last cell in column K with data and assign it a variable. If that variable is over $10,000 then I can send the e-mail to a hard coded address. If it's over, I just use my original code.

View 9 Replies View Related

Paste Special And Assigning Macro To Cell

Aug 8, 2012

I have written the code below. And I found two problems:

1.The code stopped to work when I change from Paste to PasteSpecial.

Sub Copyfriction()
Dim DestWB As Workbook: Set DestWB = ActiveWorkbook
Dim DestWS As Worksheet: Set DestWS = ActiveSheet
Dim DestCLL As Range: Set DestCLL = ActiveCell
Dim UserChoice
UserChoice = Application.GetOpenFilename(FileFilter:="Text Files (*.xls),*.xls")

[Code] ........

2.As you see the right cell on the the destination sheet need to be selected. So Im planing to run the macro when double clicking the cell in question. I have tried the next

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Address = "$A$6" Then

Cancel = True
End If
End Sub

View 1 Replies View Related

Assigning An Array Element To Select A Different Cell -Help

Jul 7, 2006

I am trying to assign an array element to select a specific cell then assign a value to it. Below is the general code that I am working with. Does anyone know why this is not working?....

View 8 Replies View Related

Assigning Inputbox Value To Cell Works In 2004 Not 2007

Nov 20, 2009

I'm working on a tracking spreadsheet that needs to be able to function on both Excel 2004 for Mac as well as Excel 2007 for PC. The code below is written to ask for a date when the status of a particular row of cells is changed to a value other than "". These cells have validation lists where the list itself is just below the cell in hidden rows. It then places the date entered in the corresponding cell to the right of the list choice in the hidden rows and the top row is simply a vlookup to show the date with the status choice.

This way, when the status cell is changed again, it retains the date in the hidden rows while setting a different one. The code below is obviously not the full code, the whole code includes other functions and is a bit long, but I've narrowed it down to this:

View 5 Replies View Related

Assigning Label Names Based On A Range Of Cell Values

Feb 20, 2007

I searched and found that to assign a name to a label based on a cell value requires the following

Label1.Caption = Worksheets("Sheet1").Range("A1").Value

which would assign the value in cell A1 as the caption for Label1. I've got a range of values in cells:


and I'd like to assign them as names to labels 1 through 12. How would I do that using VBA?

View 6 Replies View Related

Assigning Certain Cell Ranges Depending On What Is Chosen In Dropdown List Of 20 Things

Jul 16, 2009

I see that IF statements can only be nested 7 times. How do I create a cell that checks to see what item was selected in the dropdown box of 20 different choices and choose a particular cell range depending on what is chosen?

View 9 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

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

View 3 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

Sep 6, 2008

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!

View 9 Replies View Related

DBGrid Connection Through ADO (SQL)

Jul 18, 2009

to retrieve the data from MS access table in to the DB Grid at user form through MS SQL Query.

I am trying to use ADO for the same.

View 9 Replies View Related

Workbook Connection

Jun 8, 2006

i have two workbooks that use a identical worksheet which is called products (this sheet contains rows of over 19000 products ). The problem is i cannot merge the two workbooks together because they are just too big in size.

Also these workbooks have similar userforms and macros that rely on this product databases (worksheets).

My problem is when i add a new product to of one product databases i have to also add it to the second product database again. Is there a way where i can update both worksheets together without opening both workbooks.

I have three userforms that rely on this worksheet from both workbooks:

frmNewProduct - To add a new product to the worksheet.

frmProductUpdate - To edit, replace or delete products in the worksheet.

frmSearch - To search for products inside the worksheet.

I have also got other define names, worksheets and formulas that rely on these product database worksheets.

View 9 Replies View Related

SAP Connection - Which References

Sep 7, 2006

I'm trying to connect to SAP R/3, and I think I'm not sure which references (Tools --> References) I'm supposed have checked.

All I want to do for now is the following:

Dim sapConn As Object
Set sapConn = CreateObject("SAP. Functions")

Unfortunately, I am getting a "Run-time error '-2147024770 (8007007e)': Automation error. The specified module could not be found."

I have read somewhere random on the internet that I should add "SAP:Remote Function Call: COM Support 1.0 Type Library" which links to the SAPGui Provided library librfc32.dll. However, this alone doesn't seem to work...

View 5 Replies View Related

Query An Access Query With VBA Function

Jul 8, 2008

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.

View 9 Replies View Related

Edit Query Using Query Wizard

Apr 25, 2006

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

View 2 Replies View Related

How To Remove Data Connection

Aug 4, 2014

I was messing around with data connections and importing stuff from a network in real time. Cool stuff. But everytime I open ANY excel worksheet on this computer, it tells me "file cannot be found" and refers to one of the first files I used for this. this happens no matter what workbook I am opening.

When I click data --> connections its empty.

View 2 Replies View Related

DB Connection Lockout Other DB Connections

Mar 7, 2007

I have a series of external database connections that I run in Excel, connecting to a Server based DB and running some SELECT statements. (ingres). We may have up to dozen of client machines accessing the ingres DB at any time. All is good at this point but when excel goes to INSERT some calculated data back to the main database, it appears that my Excel spreadsheet connection is locking the database when it connects, keeping other people from using their client connections.

first question: Does anyone know if the connection excel uses to run sql statements via external database connection will indeed prevent other users from connecting to the main database?

second question: if so, is there a setting or something that can make excel more "passive"?

View 9 Replies View Related

VPN Connection Causes Macro Error

Jul 11, 2014

This file connects to a weather API. The laptop I am using connects through a VPN connection. For some reason whenever I am on the VPN if I try to refresh the information I will get an error and the macro will fault out. Other than that the macro works perfectly fine.

Attached is the file in question : Weather Forecast Spreadsheet Draft V12.xlsm‎

When debugging the error goes to:

How can I place in an error management instead of the code just getting blocked out.

View 1 Replies View Related

Can't Remove Public ADO Connection

Feb 12, 2009

I have a VBA application that uses data from a sheet in the excel workbook. It accesses this data with an ADO connection. On workbook open I open this connection--I declare it as public variable so I can continue to use it throughout the session. My problem is that the connection really doesn't ever dissappear. I have the problem that once I close the workbook, the project still exists in the VB Editor. How do I get rid of the connection? I am stuck because I declared it as public? I want to keep it open, rather than opening every time I want to use it, because it is faster to keep the connection open.

My code is below.

Option Explicit 'requires variable declaration
'declare global connection
Public cn As ADODB.Connection

Sub OpenDBConnection()
'open db connection
'this happens on workbook open
If cn Is Nothing Then
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=" & WCHSBook.FullName & ";" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"""
End With
End If
End Sub

View 9 Replies View Related

Copyrights 2005-15, All rights reserved