How to query an existing query

[gglgiggles]

I have a spreadsheet that needs to be shared. I use a query to get data from as database using a stored procedure. My problem is that I need to seperate this data into 4 seperate sheet based upon the data that is returned. I have this working by doing an advanced filter and then doing a copy to the seperate sheets. Unfortunately when I share the workbook, it fails as some limitations exist when a workbook is shared. I am wondering if I can perform a query against the query or is there another suggestion that someone can suggest.

I have included the code although I know it doesn't work in my situation.

Sub filter(crit As String, sht As String)
'
' filter Macro
'
'
'Worksheets("All Data").Range("a4", lastcell).
Worksheets("All Data").Activate
Worksheets("All Data").Range("a4:aj499"). _
AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range(crit), _
CopyToRange:=Range("a500"), Unique:=False
Worksheets(sht).Rows("5:500").ClearContents
Worksheets("All Data").Range("a501:aj1000"). _
Copy (Sheets(sht).Range("A5"))
Worksheets("All Data").Rows("500:1000").Delete
End Sub
'Trd_Stl_Today
'Stl_Today
'Stl_GT_Today
'Today_Repo



Sub create_query()
Dim oCn 'As ADODB.Connection
Dim oRS 'As ADODB.Recordset
Dim ConnString As String
Dim SQL As String
Dim qt As QueryTable
ConnString = "DSN=NYPD_PRICEADV"
Set oCn = CreateObject("ADODB.Connection")
oCn.ConnectionString = ConnString
oCn.Open
SQL = "call ap.block_blotter();"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open
Set qt = Worksheet("All Data").ActiveSheet.QueryTables.Add(Connection:=oRS, _
Destination:=Worksheet("All Data").Range("a5"))
qt.Name = "Block Blotter"
qt.FieldNames = False
qt.RowNumbers = False
qt.FillAdjacentFormulas = True
qt.PreserveFormatting = True
qt.RefreshOnFileOpen = False
qt.BackgroundQuery = True
qt.RefreshStyle = xlOverwriteCells
qt.SavePassword = True
qt.SaveAll Data = True
qt.AdjustColumnWidth = False
qt.RefreshPeriod = 2
qt.PreserveColumnInfo = True
qt.BackgroundQuery = True
qt.Refresh
End Sub

Sub start_proc()
Dim loc As Worksheet
Set loc = ActiveSheet
ActiveWorkbook.Save
For Each qt In Worksheets("All Data").QueryTables
qt.Refresh
Next
Call filter("Trd_Stl_Today", "Trd Stl Today")
Call filter("Stl_Today", "Stl Today")
Call filter("Stl_GT_Today", "Stl > Today")
Call filter("Today_Repo", "Today Repo")
loc.Select
'ActiveWorkbook.Save
End Sub

 

[Derek Brown]

Hello and welcome to The Board.
I don't like shared workbooks as they do present a number of probems.
If you haven't already read it, have a look at
http://office.microsoft.com/en-us/excel-help/administration-of-shared-workbooks-HA001013057.aspx
(it was written in the days of Excel 2002 but much of it is still relevant in later versions).
Searching Excel Help for "Share a workbook" will get you to a list of limitations.
I use Excel 2007 at work and would use queries in the Microsoft Access database to provide the data, linked to the Excel workbook so that it refreshes on opening. In Access you could have a number of queries to 'filter' another query, and linked to different worksheets.
HOWEVER, I don't know if that would work for Shared workbooks.

 

[xenou]

Hi,
Can you step through your code and say precisely what fails when it runs? I'm not sure I see what about filtering/copying shouldn't work.

On stepping through code:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

As far as querying your query you could create a query table with your worksheet as the source, after you've populated it from the database. This might work best if you create a new ODBC data source with your workbook as the data source - then run a query and choose it. I don't do this much though so I'm not a big help here. It would be great if your IT support could allow you to run your stored procedure with parameters - so you could run the stored procedure for the four subsets you need from it. If it were me, I'd probably run the SQL stored procedure query in a separate workbook and then ping it from the shared workbook with four queries set up for each tab.

However, I do think your original idea should have worked - so if you get some clues as to what's failing we can maybe fix it without any more querying.

BTW, I've never understood why we add query tables all the time with such code - can't the query simply be refreshed? "Add once, use often" - that's what I always say (not really).

 

[gglgiggles]

The issue is definately related to the shared workbook. The copies and advanced find do not work while shared. I have redone the workbook only use the autofilter which is available while shared.

Thanks for your replies.