How to query an existing query

gglgiggles

New Member
Joined
Aug 11, 2010
Messages
2
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
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
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.
 
Upvote 0
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).
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top