Free Microsoft Excel 2013 Quick Reference

Cell Reference parameters used in Excel SQL Queries

Hi,

I am extracting data from an SQL database into Excel 2007 using MS Query.

I've used the following SQL code to prompt for the parameter:

"WHERE
casosdeuda.ConceptoDeuda LIKE (3) and
a.`TipoCaso` in (?,?) and
a.`TipoDeuda` in (?,?)"

Using Data/Connections/Properties/Definitions/Parameters, I am using "Get the value from the following cell" to plug in the parameter. This works perfectly until I save and close the file.

When the file is opened I need to link up the cell references again. How do i save the cell reference parameters so they don't have to be entered each time the file is opened??
Can anyone help?
thanks

-alex


I'm trying to use Microsoft Query within Excel to write a SQL query against
a data set.

SELECT e.EVENTNAME, e.EVENTDATE
FROM EVENT e
WHERE (e.EVENTDATE >= DATE()
ORDER BY e.EVENTDATE

In the above, DATE() is not correct to get the current system date. I tried
DATE, NOW, NOW(), GETDATE, GETDATE(), TODAY, TODAY().. now of which worked.

Can someoine please tell me the correct syntax?

Thanks!
Ron

Hi,

I am not good in programming, but the clarity of the postings from the forum is making me to understand and implement to my work environment, now I have a query regarding worksheet function (Count IF) using in Excel VBA, I kindly request to help me in solving this

Query:-

I will be updating a data in sheet1 From column "A" to Column "Y",which I will be updating daily, cells contain a text, , I want to use a count if function in column “Z” up to the last row of the data updated in column “A” , formula should return the results like :

Z1 = countif(A1:Y1,”Text”)
Z2=Countif(A2:Y2,”Text”)
.
.
.
Z100=countif(A100:Y100,”Text”)

If i drag the formula, its occupies more storage and slowdowns the application, so kindly to help me in writing VBA code the above function.

Thanks in advance

Regards
GV Reddy

I need to define cusips in excel in an if/nested if statement

Example

Cell A1=005482J86
Cell A2=0162483G9

How do i use and if/or logic statement to make a true statement equal "Muni
bond" (cell b1) or "Corp Bond"(cell b2)

I cannot get excel to recognize any logic statement since a cusip has
numbers and text!!

Please help!
Thanks in advance !!

With the below code I'm trying to run a SQL query and return the results in the worksheet 'LLL'. However, what is the VBA code that actually returns the results in the worksheet? The macro runs without errors but no data in worksheet 'LLL'. I didn't paste the SQL query itself as it's not relevant and quite long.

Many thanks for your help.


	VB:
	
 New_LLL() 
    ThisWorkbook.Activate 
     
     'First clear the contents from the query
    Worksheets("LLL").Select 
    Range("A2").Select 
    Do Until ActiveCell = "" 
        ActiveCell.Offset(1).Select 
    Loop 
    Range("A4", ActiveCell.Offset(-1, 3)).ClearContents 
     
     'Get reporting date
    ReportingDate = ThisWorkbook.Sheets("Parameters").Range("D1") 
     
     'Format the value for use in the SQL query
    ReportingDateFor = Format(ReportingDate, "yyyy-mm-dd") 
     
    Worksheets("LLL").Select 
    Range("A1").Select 
     
    Dim cnn As New ADODB.Connection 
    Dim rst As New ADODB.Recordset 
    Dim StrQuery1 As String 
    Dim ConnectionString As String 
     
     'Connection to SQl Server
    ConnectionString = "Provider=SQLOLEDB.1;" & _ 
    "Integrated Security=SSPI;" & _ 
    "Initial Catalog=PPFSDB;" & _ 
    "Data Source=PBFIB03C00SQL01" 
    cnn.Open ConnectionString 
    cnn.CommandTimeout = 900 
     
     'Queries to be executed
    StrQuery1 = StrQuery1 & "XXX" 
     
    rst.Open StrQuery1, cnn, adOpenForwardOnly, adLockReadOnly 
    rst.Close 
     
    Debug.Print "StrQuery1:"; StrQuery1 
    cnn.Close 
     
    ThisWorkbook.Sheets("Central Dashboard").Select 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


What is DBRW as used in Excel cell formula ie =DBRW(

Hi All,

Can any one please help me with Excel VB script which locks cell after single use in shared work book.

Thanks,
Yaswanth

I need to define cusips in excel in an if/nested if statement

Example

Cell A1=005482J86
Cell A2=0162483G9

How do i use and if/or logic statement to make a true statement equal "Muni
bond" (cell b1) or "Corp Bond"(cell b2)

I cannot get excel to recognize any logic statement since a cusip has
numbers and text!!

Please help!
Thanks in advance !!

Hello,

I had a problem with the reference to outlook in Excel
(each user has different version of office).
The solution you suggested works great.
I think it is called dynamic reference:
Set objoL = New Outlook.Application
Set objMail = objoL.CreateItem(olMailItem)
Now I have the same problem with power point.
I wish to open power point, create new presentation and add slides without using reference
to power point.

I am using the following code:
On Error Resume Next
   Set ppApp = GetObject(, "PowerPoint.Application")
   If Err.Number <> 0 Then
       Set ppApp = CreateObject("Powerpoint.Application")
       ppApp.Presentations.Add
   End If
How can I implement it without using reference to power point ?

Thank you for your help

Hello,

I'm stuck at a roadblock with a report i'm writing. 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.

Code:
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

Trying to have a query that can set a constant in the select statement, for
example:
SELECT Author, '11/15/2005' as Updated, PublishDate
FROM Author
WHERE Author like 'A*'

Can I prompt the user for this value as an input, for example:
SELECT Author, ? as Updated, PublishDate
FROM Author
WHERE Author like 'A*'

Maybe with a prompt "Please enter Updated date".
Before resorting to VBA or Access, just want to see if it can be done in
Excel.

Where could I find a list of the commands that are used in excel 2003 macro's?

can auto text be used in excell like it can in word?

How do I make the contents of a cell on one page in excel equal to another
cell on another page in excel?

What do you mean by this line:

2. Select Home-> Conditional Formatting (in Styles Group).

I can't find Style Group under Conditional Formatting.

http://www.excelforum.com/tips-and-tutorials/621369-applying-formatting-to-cells-containing-text-only-in-excel-2007-a.html

Regards,

Ganesh P

Can anyone explain the rules for Excel SQL queries if they are.
For exampe is Inner/Outer join possible ?

Hi,

How can i protect cell data against modifications in excel sheet? as i would like to prepare a template which can not be modify by other user. Please let me know the solution ASAP so that it would be a great help.

Thanks,
Prasad

Hi All,

Can any one please help me with VB script which locks cell after single use in a shared work book?

Yaswanth

How do I hide a ROW if cell value is zero in Excel

Hi

I need to know how to select different cells to be used in a formula, but i
don't know what is the separator that i need to use to this, see the example
bellow

Cells that i need to include on my formula:

A1
C5
BH32

Thanks.

I see a good many questions nowadays about how we can use controls on
a User Form to dictate changes to a SQL query for returning records to
Excel from a database.

Good news I guess... It means more people are getting into using VBA & ADO
to talk to their databases.

Anyway, I thought I would post up an example of how you can use UserForm controls
to determine a SQL query to either return ALL records from a table or
via use of a checkbox and combobox, filter the records to return only a subset.

The attached example uses the Northwind database, as this comes as standard with
any Access installation. You may need to amend the path to your copy of Northwind.
To do this, edit the stCon variable at the top of the UserForm code module (shown below
also).

Make sure you also have a reference set to Microsoft ActiveX Data Components v2.5 or later
in your VBE.

For those who cannot download files/attachments... the code is displayed below.

With thanks as always to my friend Dennis Wallentin, especially for teaching me about
Error Checking methods with ADO.


	VB:
	
 
 '#########################################################
 '#                                                       #
 '# References need to be set in the VBE to the following #
 '# reference libraries:-                                 #
 '# Microsoft ActiveX Data Objects 2.5 or > Library       #
 '#                                                       #
 '#########################################################
 
 'You may also need to amend the path to Northwind Database in the connection string below
 
Const stCon    As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ 
"Data Source=C:Program FilesMicrosoft OfficeOFFICE11SAMPLESNorthwind.mdb;" & _ 
"Persist Security Info=False" 
 
 
Private Sub cmdClose_Click() 
     'close the form
    Unload Me 
End Sub 
 
Private Sub chkYr_Click() 
     'This is where you can add a filter by the year
    Dim stSQL  As String 
    Dim cnt    As ADODB.Connection 
    Dim rst    As ADODB.Recordset 
    Dim vaData As Variant 
     
     'Just select the Distinct Years from Orders Table to load into Year Combobox
    stSQL = "SELECT DISTINCT DatePart(""yyyy"",[OrderDate]) FROM ORDERS;" 
     
    If chkYr.Value = True Then 
         'if the year filter checkbox is checked
        Set cnt = New ADODB.Connection 
        Set rst = New ADODB.Recordset 
        cnt.ConnectionString = stCon 
        With cnt 
            .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
            .Open stCon 'Open connection.
             'Execute the SQL statement.
            Set rst = .Execute(stSQL) 
        End With 
        With rst 
            Set .ActiveConnection = Nothing 'Disconnect the recordset.
             'Populate the array with the whole recordset.
            vaData = .GetRows 
        End With 
         'Close the connection.
        cnt.Close 
        With Me 
            With .cmbYr 
                .Clear 
                 'load the query result into combobox
                .List = Application.Transpose(vaData) 
                .ListIndex = -1 
            End With 
        End With 
    Else 
        With Me 
            With .cmbYr 
                .Clear 
            End With 
        End With 
    End If 
End Sub 
 
Private Sub cmdQuery_Click() 
     'run query to find records
    Dim stParam As String, stParam2 As String 
    Dim stSQL  As String 
    Dim cnt    As ADODB.Connection 
    Dim rst    As ADODB.Recordset 
    Dim fld    As ADODB.Field 
    Dim wsSheet As Worksheet, wbBook As Workbook 
    Dim i As Long, j As Long, x As Integer 
     
     'initial SQL to return all records
    stSQL = "SELECT * FROM ORDERS" 
     
     'set the parameter strings
    stParam = " WHERE DatePart(""yyyy"",[OrderDate]) = " & Me.cmbYr.Text 
    stParam2 = " ;" 
     
     'check & build variable parameters
     'depending on whether checkbox ticked by user
    If Me.chkYr.Value = True Then 
        stSQL = stSQL & stParam & stParam2 
    Else: stSQL = stSQL & stParam2 
    End If 
     
    On Error Goto ErrHandle 
     
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
     
    Set wbBook = ThisWorkbook 
    Set wsSheet = ThisWorkbook.Worksheets(1) 
     
    With cnt 
        .ConnectionString = stCon 
        .Open 
    End With 
     
    With rst 
        .CursorLocation = adUseClient 
        .Open stSQL, cnt, adOpenStatic, adLockReadOnly 
        .ActiveConnection = Nothing 'Here we disconnect the recordset.
        j = .Fields.Count 
        i = .RecordCount 
    End With 
     
    With wsSheet 
        .UsedRange.Clear 
        If i = 0 Then Goto i_Err 
         'Write the fieldnames to the fifth row in the worksheet
        For x = 0 To j - 1 
            .Cells(5, x + 1).Value = rst.Fields(x).Name 
        Next x 
         'Dump the data to the worksheet.
        .Cells(6, 1).CopyFromRecordset rst 
    End With 
     
    If CBool(rst.State And adStateOpen) = True Then rst.Close 
    Set rst = Nothing 
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close 
    Set cnt = Nothing 
     
ExitHere: 
    Exit Sub 
     
ErrHandle: 
    Dim cnErrors As ADODB.Errors 
    Dim ErrorItem As ADODB.Error 
    Dim stError As String 
     
    Set cnErrors = cnt.Errors 
     
    With Err 
        stError = stError & vbCrLf & "VBA Error # : " & CStr(.Number) 
        stError = stError & vbCrLf & "Generated by : " & .Source 
        stError = stError & vbCrLf & "Description : " & .Description 
    End With 
     
    For Each ErrorItem In cnErrors 
        With ErrorItem 
            stError = stError & vbCrLf & "ADO error # : " & CStr(.Number) 
            stError = stError & vbCrLf & "Description : " & .Description 
            stError = stError & vbCrLf & "Source : " & .Source 
            stError = stError & vbCrLf & "SQL State : " & .SqlState 
        End With 
    Next ErrorItem 
    MsgBox stError, vbCritical, "SystemError" 
    Resume ExitHere 
     
i_Err: 
    MsgBox "There are no records for this Query" 
    Goto ExitHere 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have used Parameters w/ ODBC MS Query, but would like to use Parameters
with an OLE connection to SQL Server... does not seems to work gives error
"No Value Given for one or More Parameters"

Ideally it would be great to refer to a Excel Range name in the OLE Query to
get the Paramaters.

Thanks in Advance

Hi Guys

I wonder if you can help with this one.

I'm pulling some live call data into Excel from a CRM system using MS query - I'm getting the last three months call data using a criteria ([TodaysDate]-90 in the created on field) but I was wondering how you used an excel value as a parameter.

At the moment I have to type todays date in every time I run the query but I know I can reference todays date in excel (=today()) and I want to use that as the query parameter. I've seen it done before in other spreadsheets but I'm not sure how.

I hope that's reasonably clear - I know it's not a full on Excel enquiry but I thought you guys might be able to help.

Thanks as usual.
Stu

Hi,
A macro with SQL code reads cell in my Excel and use it in SQL WHERE statment:
The cell should include system serial number.
I need the SQL query to read all the serial numbers.
What should I write in the cell?

The SQL code looks like that:

...
WHERE
(tbl.mr.[sysCode]=55)
AND (tbl.mr.[sysNum]=?)

For the ? parameter, I use cell in my Excel.

Tnx!

Hi People

I have the following requirement. I have searched the forum and a few posts came close but I still could not find what I need.

I am not a programmer and my knowledge of macros goes about as far as recording them and linking them to a button.

I have created a sheet in Excel 2007.
In cell A1 I have a list of 3 server names. (Call tem server01/02/03)
In cell A2 I have a list of 7 databases. (Call them db01/02…07)

The report have a refresh button the user can click to refresh the data. (Calling the refresh all macro)

The names are user friendly names. In other words, AdminData would point to server svrmain and SalesData to svrdata01 (Get the idée?)
The same with the database.
The user would choose Sydney that point to database syddata and when choosing Adelaide it would point to addata etc

The actual selection in the server could be from a table(s) and/or view(s) and/or stored procedure(s) but it would stay constant per report.

So what we have so far is a user opening a specific report (of many) that is linked to , let’s say a table called table01. Depending on what the user enter in Cell A1 and A2 the user control from what server and database the contents of Table01 is returned from. If opening another report that report would be lined to a diff table or view etc)
(I use the word linked but the meaning is the table that would supply the data for the report.)

Second important thing (I think) is that we are looking at different SQL servers 2000, 2005, 2008.
At the moment I have an ODBC link setup for every server and every database depending on the native drivers I use.
I use the following naming convention for the ODBC names <servername>_<native client><database>
Server01_00datbase01..07 Sql2000 Using Native SQL drivers for it with the 7 databases
Server02_05datbase01..07 Sql2005 Using Native SQL drivers 9 for it with the 7 databases
Server03_10datbase01..07 Sql2008 Using Native SQL drivers 10 for it with the 7 databases

All the servers use the same username and password to access the data
ReadOnlyUser
Password

So currently looking at any 21 of the same reports (3 servers x 7 databases) the only difference is the connection string

What I was thinking of doing but have no idée how to do it was the following:

Based on what the user enter in A1 and A2 to dynamically change the connection string so that Excel point to the correct ODBC and/or update the connect string as required per the A1 A2 entry.

Obviously if there is better ways to do feel free to advise.

The part that have to stay constant is the user entering the server and database to be used and the user must have the control to decide when a refresh should happen
(Other parameters is passed but that is used in the select and does not form part of the source pointing)

I have tried to give as much info as I could. If something is short, please feel free to ask.

Please not the macro's must be 2007 compatable

Regards
Burger