Excel 2007 - Auto Query Sql Server based on values in Worksheet Range 1

BxWill · Dec 14, 2010

Have a Excel 2007 Workbook with 3 worksheets.

Worksheet 1 has a range of 2400 unique account numbers that I maintain on a weekly basis - adding additional account numbers daily in column A while ensuring that all numbers are unique.

Is it possible, via VBA, to populate the remaining two worksheets with the query results from two distinct separate queries and have the query results refresh on a weekly basis?

Query 1 - Amt paid to date for each account number

Query 2 - Most recent address for each account number

Realize that this may be relatively easy to perform using MS Access but would like to explore various MS
Excel solutions before using MS Access due to the skill level of the Manager...

Did initially explore the use of MS Query but could not readily locate information regarding the extraction of data based on a range of values in column A.

SkipVought · Dec 15, 2010

but could not readily locate information regarding the extraction of data based on a range of values in column A.

If you use ANY sort of SQL based query, every column of data must have a heading or field name. Make sure your column A has a heading.

Then you can query, using MS Query, to your heart's content, returning the resutset to any sheet of your choice. These queries can be refreshed at any time, on demand of automatically.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BxWill · Dec 15, 2010

All columns have headings.

Familiar with MS Query but prefer a solution involving vba.

Currently reviewing user defined functions but it appears that I may not be able to return multiple columns...

The review continues...

Is there a vba-based solution that will extract data, into worksheets 2 and 3, from sql server using the values in a column on another Excel worksheet?

SkipVought · Dec 15, 2010

Familiar with MS Query but prefer a solution involving vba.

WHY?

You might use VBA as part of the MS Query solution, but it may not be necessary.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BxWill · Dec 15, 2010

The sql syntax of the MS Query to extract from an oracle database looks like this;

SELECT GCS_VTAC_CUSTOMER.ACCT_ID
, GCS_VTAC_CUSTOMER.VISIT_DT
, GCS_VTAC_CUSTOMER.AUTHOR_DT
, GCS_VTAC_CUSTOMER.CONTACT
FROM GCS_VTAC_CUSTOMER
WHERE (GCS_VTAC_CUSTOMER.ACCT_ID In ('458723','112541'))
ORDER BY GCS_VTAC_CUSTOMER.ACCT_ID

How do I enable the MS Query to extract the Acct IDs from the range in column A of Worksheet 1 instead of typing in the acct IDs every time I refresh the query?

Note, I plan to have a dynamic range on column A of Worksheet 1.

SkipVought · Dec 15, 2010

Use this function in your SQL string...

Code:

Function MakeList(rng As Range, Optional QUO As String = "'", Optional COM As String = ",") As String
'SkipVought
'returns an APSTROPHY delimited list separated by COMMA
    Dim r As Range
    
    For Each r In rng.SpecialCells(xlCellTypeVisible)
        MakeList = MakeList & QUO & r.Value & QUO & COM
    Next
    MakeList = Left(MakeList, Len(MakeList) - Len(COM))
End Function

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

SkipVought · Dec 15, 2010

...like this...

Code:

    Dim sSQL As String
    
    sSQL = "SELECT"
    sSQL = sSQL & "  ACCT_ID"
    sSQL = sSQL & ", VISIT_DT"
    sSQL = sSQL & ", AUTHOR_DT"
    sSQL = sSQL & ", CONTACT"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM GCS_VTAC_CUSTOMER"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE ACCT_ID In ([b]" & MakeList([YourRange]) & "[/b])"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "ORDER BY ACCT_ID"

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BxWill · Dec 17, 2010

Still not able to get this to work.

Did insert the function into the MS Query sql.

Receive error ORA - 01756 String not properly terminated

Currently reviewing...

Also assume that "YourRange" should be the dynamic range name in column A on worksheet 1.

Any additional insight?

Is this method more preferable than using vba with ADO?

SkipVought · Dec 17, 2010

Is this method more preferable than using vba with ADO?

It can be used with ADO, and I do.

It can be used with MS Query, and I do.

It can be used on a sheet, and I do.

Did insert the function into the MS Query sql.

HOW did you do that?

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BxWill · Dec 17, 2010

The steps I use to modify the MS Query are;

Access the worksheet that display the query results
Click "Get External Data"
Click "Connections"
Highlight the workbook connection
Click "Properties"
Click the tab titled "Definition"
Click "Edit Query"
Enter the password to access the oracle database

Prompted with "This query cannot be edited by the Query Wizard"

I click "OK"

Now, I am presented with the MS Query pane
Then, I click "SQL" on the task bar at top and enter the function and revised sql (and delete the sql that I had entered initially)

Is it possible to briefly outline the steps to accomplish the objective using vba as well as with MS Query?

Any additionally insight is greatly appreciated.

SkipVought · Dec 17, 2010

The problem is that MS Query knows NOTHING about your VB code and functions.

If you modify the code manually, via the MS Query UI, YOU must supply the string that gets inserted between the PARENTHESES. That's OK in my book for an ad hoc query where you need a list, or if your list is static. In this case, get the string returned in a cell on your sheet and COPY the value -- then activate the MS Query window (already opened) and PASTE it into your SQL code.

Otherwise, you must do the entire thing via VBA like this example...

Code:

Sub IV()
    Dim sSQL As String
    
    sSQL = sSQL & "SELECT"
    sSQL = sSQL & "  PARTNO_201 AS IV_PN"
    sSQL = sSQL & ", SUM(QTYONDOK_233+STKITQTY_233+STKOHQTY_233) AS IV_QTY"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM FRH_MRP.PSK02233"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE PARTNO_201 IN ([b]" & MakeList([Part_Number]) & "[/b])"
    sSQL = sSQL & "  AND STORETYP_233<>'W'"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "GROUP BY PARTNO_201"

    Debug.Print sSQL

    With wsIV.ListObjects(1).QueryTable
        .CommandText = sSQL
        .Refresh False
    End With
End Sub

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BxWill · Dec 17, 2010

Thanks for the insight.

However, I would like the data from Oracle or sql server to populate worksheets 2 and 3 beginning at location A5.

As I type the unique values in column A of worksheet 1, the data should automatically populate worksheets 2 and 3 beginning at cell location A5 and refresh upon opening the Excel workbook.

It appears that I need to specify additional information in the code to open the Oracle database and extract the data to the desired location in the worksheets.

Will continue to search and post back...

SkipVought · Dec 17, 2010

Here are the elements you need to consider:

1. your criteria list on sheet 1

2. your querytable on sheet 2 which will return data starting at whatever range you designate when you place the querytable on the sheet,

3. if your criteria list is dynamic, you might want to execute the query with VBA code, similar to the code I posted, that access an Oracle database.

It appears that I need to specify additional information in the code to open the Oracle database and extract the data to the desired location in the worksheets.

NO!

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BxWill · Dec 18, 2010

Ok.

Appreciate the insight and patience.

Will focus on using VBA (instead of MS Query) due to the need to have a dynamic range in column A of worksheet 1.

Due to not being proficient in VBA, this is taking longer than initially planned. However, my goal is to complete today!

What I have for the first query on worksheet 2 is below. I will begin to troubleshoot the first query but decided to post what I have so far to generate any feedback as to the structure of the code...

After successfully generating the first query, I need to complete query 2 on worksheet 3.

Will continue to research but appreciate any additional insight that will accomplish the desired result - query results from Oracle in Worksheets 2 and 3 based on a dynamic range of account IDs in column A of Worksheet 1.

Thanks in advance.

Code:

Function MakeList(rng As Range, Optional QUO As String = "'", Optional COM As String = ",") As String
'Test
'returns an APSTROPHY delimited list separated by COMMA    
	Dim r As Range        
	For Each r In rng.SpecialCells(xlCellTypeVisible)
        	MakeList = MakeList & QUO & r.Value & QUO & COM    
	Next    
	MakeList = Left(MakeList, Len(MakeList) - Len(COM))
End Function

Code:

Sub OracleToExcel()        
Dim Database_Name As String    
Dim User_ID As String    
Dim Password As String    
Dim sSQL As String    
Dim OraDynaSet As Object    
Dim objSession As Object    
Dim objDataBase As Object    
Dim r As Range    
Const adOpenStatic = 3    
Const adLockOptimistic = 3    
Set objConnection = CreateObject("ADODB.Connection")    
Set objRecordset = CreateObject("ADODB.Recordset")    

objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & 
'Create a reference to my database    
Database_Name = "DataWarehouse"     
User_ID = "FDavo3"     
Password = "VTrinc24"     

'Create a reference to the OO4O dll    

Set objSession = CreateObject("OracleInProcServer.XOraSession")    
Set objDataBase = objSession.OpenDatabase(Database_Name, User_ID & "/" & Password, 0&)            


'Range name for list on col A in worksheet 1 = "DynAcctList"

sSQL = "SELECT"    
sSQL = sSQL & "  ACCT_ID"    
sSQL = sSQL & ", VISIT_DT"    
sSQL = sSQL & ", AUTHOR_DT"    
sSQL = sSQL & ", CONTACT"    
sSQL = sSQL & vbLf    
sSQL = sSQL & "FROM GCS_VTAC_CUSTOMER"    
sSQL = sSQL & vbLf    
sSQL = sSQL & "WHERE ACCT_ID In (" & MakeList([DynAcctList]) & ")"    
sSQL = sSQL & vbLf    sSQL = sSQL & "ORDER BY ACCT_ID"

MsgBox sSQL & vbCrLf & OraDynaSet.RecordCount

Debug.Print sSQL

End Sub

SkipVought · Dec 18, 2010

You are making this entirely too difficult.

1. place a querytable on your sheet by doing data > Get External Data... use the OraHome or Microsoft Oracle Driver to connect, Add your table(s) and select your data and criteria, and File > return data to Excel

2. Turn on your macro recorder and EDIT the query you must performed and File > return data to Excel. and STOP the recorder.

Post your recorded code for help customizing.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BxWill · Dec 18, 2010

Both queries that will be used to populate worksheets 2 and 3 were initially set up as MS Access passthrough queries to extract data from oracle. I would run the queries and then paste the results to MS Excel.

Displayed below in Exhibit 1 is the modified query for worksheet 2 using the steps that were suggested.

Considering that this query is only extracting data from one Oracle table and not involving complicated joins, it can readily be replicated using MS Query.

The query for worksheet 3, as displayed in Exhibit 2, involves multiple tables and a left join. For queries involving multiple tables, subqueries, left and outer joins, I am not sure that setting up a Data Table using MS Query will be as "flexible" as using ADO with vba...

What modifications should be made to the code in Exhibit 1 that will allow me to extract data for the account IDs in column A on worksheet 1?

Further, is it possible to display the modified code for the query in Exhibit 2 as well?

Exhibit 1

Code:

Sub mcrEditQuery()
'
' mcrEditQuery Macro
' Edit Oracle Query
'
' Keyboard Shortcut: Ctrl+Shift+E
'
    With ActiveWorkbook.Connections("Query from BDA2T").ODBCConnection
        .BackgroundQuery = True
        .CommandText = Array( _
        "SELECT GCS_VTAC_CUSTOMER.ACCT_ID, GCS_VTAC_CUSTOMER.VISIT_DT, GCS_VTAC_CUSTOMER.DC_DT, 
GCS_VTAC_CUSTOMER.AUS_TOT, GCS_VTAC_CUSTOMER.UMIN_TOT_PAID_DAYS, GCS_VTAC_CUSTOMER.ACTUAL_STAY" & Chr(13) & "" & Chr(10) & "FROM CARC" _
        , _
        "TS.GCS_VTAC_CUSTOMER GCS_VTAC_CUSTOMER" & Chr(13) & "" & Chr(10) & "WHERE (GCS_VTAC_CUSTOMER.ACCT_ID In ('104827667','100785698','102365" _
        , "099'))" & Chr(13) & "" & Chr(10) & "ORDER BY GCS_VTAC_CUSTOMER.ACCT_ID")
        .CommandType = xlCmdSql
        .Connection = Array(Array( _
        "ODBC;DSN=BDA2T;UID=CSMITH5_RPT;;DBQ=BDA2T;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BAM=IfAllSuccessful;NUM=NLS;" _
        ), Array("DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=0;"))
        .RefreshOnFileOpen = False
        .SavePassword = False
        .SourceConnectionFile = ""
        .SourceDataFile = ""
        .ServerCredentialsMethod = xlCredentialsMethodIntegrated
        .AlwaysUseConnectionFile = False
    End With
    With ActiveWorkbook.Connections("Query from BDA2T")
        .Name = "Query from BDA2T"
        .Description = ""
    End With
    ActiveWorkbook.Connections("Query from BDA2T").Refresh
End Sub

Exhibit 2

Code:

Detail Paid Query

select distinct
CARCTS.GCS_CL_LINE .CDML_ACCT_ID
, CARCTS.GCS_MBR.FIRST_NAME
, CARCTS.GCS_MBR.LAST_NAME 
, CARCTS.GCS_CL_LINE.PRPR_ID  
, CARCTS.GCS_CLM.CLCL_ID 
,CARCTS.GCS_HSP.STATEMNT_FR_DT
,CARCTS.GCS_HSP.STATEMNT_TO_DT
,SUM(CARCTS.GCS_CL_LINE.CDML_SB_PYMT_AMT+CARCTS.GCS_CL_LINE.CDML_PR_PYMT_AMT)PYMT  
,CARCTS.CMC_CLCK_CLM_CHECK.CKPY_PAY_DT  

FROM CARCTS.GCS_CLM
INNER JOIN CARCTS.GCS_CL_LINE   
ON CARCTS.GCS_CLM.CLCL_ID = CARCTS.GCS_CL_LINE.CLCL_ID 

INNER JOIN CARCTS.GCS_SUBS 
ON CARCTS.GCS_SUBS.SBSB_CK = CARCTS.GCS_CLM.SBSB_CK
 
INNER JOIN CARCTS.GCS_MBR 
ON CARCTS.GCS_CLM.MEME_CK = CARCTS.GCS_MBR.MEME_CK 

INNER JOIN CARCTS.CMC_CLCK_CLM_CHECK
ON CARCTS.GCS_CLAIM.CLCL_ID = CARCTS.CMC_CLCK_CLM_CHECK.CLCL_ID

Left JOIN CARCTS.GCS_HSP  
ON CARCTS.GCS_CLM.CLCL_ID = CARCTS.GCS_HSP.CLCL_ID 
 
WHERE 
CARCTS.GCS_CL_LINE.CDML_ACCT_ID in ('133277685',
'100226228',
'100524423',
'100619452',
'601717711')


GROUP BY 
CARCTS.GCS_CL_LINE .CDML_ACCT_ID
, CARCTS.GCS_MBR.FIRST_NAME 
, CARCTS.GCS_MBR.LAST_NAME 
, CARCTS.GCS_CL_LINE.PRPR_ID 
, CARCTS.GCS_CLM.CLCL_ID 
,CARCTS.GCS_HSP.STATEMNT_FR_DT
,CARCTS.GCS_HSP.STATEMNT_TO_DT
,CARCTS.CMC_CLCK_CLM_CHECK.CKPY_PAY_DT

ORDER BY 
CARCTS.GCS_CL_LINE.CDML_ACCT_ID Asc
,CARCTS.GCS_HSP.STATEMNT_FR_DT
,CARCTS.GCS_HSP.STATEMNT_TO_DT;

SkipVought · Dec 19, 2010

Code:

Sub mcrEditQuery()
'
' mcrEditQuery Macro
' Edit Oracle Query
'
' Keyboard Shortcut: Ctrl+Shift+E
'
   Dim sSQL as string
sSQL = "SELECT GVC.ACCT_ID, GVC.VISIT_DT, GVC.DC_DT, GVC.AUS_TOT, GVC.UMIN_TOT_PAID_DAYS, GVC.ACTUAL_STAY" 
sSQL = sSQL & vbLf  
sSQL = sSQL & "FROM CARCTS.GCS_VTAC_CUSTOMER GVC
sSQL = sSQL & vbLf  
sSQL = sSQL & "WHERE (GVC.ACCT_ID In (" & MakeList([YourListName]) & ")" 
sSQL = sSQL & vbLf  
sSQL = sSQL & "ORDER BY GVC.ACCT_ID"

With ActiveWorkbook.ListObjects("Query from BDA2T").QueryTable
.CommandText = sSQL
.Connection = "ODBC;DSN=BDA2T;UID=CSMITH5_RPT;;DBQ=BDA2T;"
.Refresh false
End With

End Sub

Your second query you definitely did not construct in the MS Query editor. That Oracle syntax will NOT run. Paste it into the SQL window and see. In fact, you may not be able to run that outer join in MS Query. You can do outer joins, but on a limited basis. In the event that MS Query will no execute without error, you must use ADO.

Let me suggest that you take this thread to forum707 for ADO coding advice.

Skip,
_{Just traded in my old subtlety...

for a NUANCE!}

BxWill · Dec 19, 2010

Appreciate the insight.

Will transfer thread to forum 707.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Excel 2007 - Auto Query Sql Server based on values in Worksheet Range 1

MIS

Programmer

MIS

Programmer

MIS

Programmer

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Programmer

MIS

Similar threads

Log in

Part and Inventory Search

Sponsor