I have a workbook with several sheets that has a pivot table per sheet. The
pivot tables access a sql database. The data is filtered on the period. The
filter is hardcoded into Microsoft query. All the pivot tables uses the same
filter, but very disparate data.

Is there any way that I can use a variable in the query that references
cells in the workbook?

See if this link:
http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

I have a workbook with several sheets that has a pivot table per sheet. The
pivot tables access a sql database. The data is filtered on the period. The
filter is hardcoded into Microsoft query. All the pivot tables uses the same
filter, but very disparate data.

Is there any way that I can use a variable in the query that references
cells in the workbook?

Hi Ron

Thanx. That did help. I understand the logic, but now when I try to set up a
parameter on the query, it says that parameters are not allowed. Any idea why?

Thanx

Vic

"Ron Coderre" wrote:

See if this link:
http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

I have a workbook with several sheets that has a pivot table per sheet. The
pivot tables access a sql database. The data is filtered on the period. The
filter is hardcoded into Microsoft query. All the pivot tables uses the same
filter, but very disparate data.

Is there any way that I can use a variable in the query that references
cells in the workbook?

After I posted, I remembered that Pivot Tables based on External Data don't
allow parameters via the user interface. You'd need to use VBA to access the
CommandText of the PivotCache and write your parameters into the SQL there.

Alternatively, if practical, you could base the Pivot Table on data pulled
into one of your worksheet. Those queries allow parameters.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

Hi Ron

Thanx. That did help. I understand the logic, but now when I try to set up a
parameter on the query, it says that parameters are not allowed. Any idea why?

Thanx

Vic

"Ron Coderre" wrote:

See if this link:
http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

I have a workbook with several sheets that has a pivot table per sheet. The
pivot tables access a sql database. The data is filtered on the period. The
filter is hardcoded into Microsoft query. All the pivot tables uses the same
filter, but very disparate data.

Is there any way that I can use a variable in the query that references
cells in the workbook?

Thanx Ron

I was hoping to avoid having to use VBA, but seems like that is the only
recourse. The data is to much for excel to handle directly. I will have to do
VBA then.

Vic

"Ron Coderre" wrote:

After I posted, I remembered that Pivot Tables based on External Data don't
allow parameters via the user interface. You'd need to use VBA to access the
CommandText of the PivotCache and write your parameters into the SQL there.

Alternatively, if practical, you could base the Pivot Table on data pulled
into one of your worksheet. Those queries allow parameters.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

Hi Ron

Thanx. That did help. I understand the logic, but now when I try to set up a
parameter on the query, it says that parameters are not allowed. Any idea why?

Thanx

Vic

"Ron Coderre" wrote:

See if this link:
http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

I have a workbook with several sheets that has a pivot table per sheet. The
pivot tables access a sql database. The data is filtered on the period. The
filter is hardcoded into Microsoft query. All the pivot tables uses the same
filter, but very disparate data.

Is there any way that I can use a variable in the query that references
cells in the workbook?

Here's some code to get you started:

This code could be attached to a button on the worksheet:
Private Sub cmdUpdatePT_Sql_Click()
Dim pvtSlsPT As PivotTable
Dim strNewslsSql As String

Set pvtSlsPT = PivotTables("My_Pivot_Table")
strNewslsSql = [a cel reference].Value

'Call the process that changes the SQL
UpdatePivotQrySource pvtPT:=pvtSlsPT, strNewSql:=strNewslsSql

Set pvtSlsPT = Nothing
End Sub


This procedure is called by the above code
Sub UpdatePivotQrySource(pvtPT As PivotTable, strNewSql As String)
'pvtPT As PivotTable 'Pivot Table object to receive the
new SQL code
Dim strRestoreCommandSetting As String 'Holds the current Command string for
pvtPT
Dim strRestoreConnSetting As String 'Holds the current Connection string
for pvtPT
Dim strTempConnection As String 'Holds the working copy of the
connction string

'If the Connection is "ODBC"...
'-Temporarily change it to "OLEDB" (otherwise processing fails)
'-Effect the changes
'-Revert the connection back to "ODBC"

With pvtPT.PivotCache

'Read the connection string from pvtPT
strRestoreConnSetting = .Connection
strTempConnection = strRestoreConnSetting

strRestoreCommandSetting = .CommandText

'Write the changes to the appropriate Pivot Table setting
On Error GoTo err_Handler

strTempConnection = Replace(strRestoreConnSetting, "ODBC", "OLEDB",
1, 1)

.Connection = strTempConnection
.CommandText = strNewSql

'Restore the original connection string
.Connection = strRestoreConnSetting

On Error GoTo err_Handler

'Refresh the data
pvtPT.RefreshTable
End With

Set pvtPT = Nothing

MsgBox "Pivot table updated successfully"

Exit Sub

err_Handler:
'The update failed
application.ScreenUpdating = True

On Error GoTo err_Handler2
With pvtPT.PivotCache
'If the Connection string begins with "ODBC"
'it must be temporarily change to "OLEDB" to allow the CommandText
changes
'...then reset back to "ODBC"
strTempConnection = Replace(strRestoreConnSetting, "ODBC", "OLEDB", 1,
1)
.Connection = strTempConnection

'Set the CommandText to the pre-adjustement value
.CommandText = strRestoreCommandSetting

'Set the Connection to the pre-adjustement value
.Connection = strRestoreConnSetting

End With

Set pvtPT = Nothing

MsgBox _
Title:="UPDATE ERROR", _
Prompt:="The changes could not be implemented. Check for proper
syntax." & vbCr & vbCr _
& "The previous settings have been restored.", _
Buttons:=vbOKOnly + vbCritical
Exit Sub

err_Handler2:
MsgBox _
Title:="UPDATE ERROR", _
Prompt:="The changes could not be implemented and " & vbCr _
& "errors occurred in attempting to restore previous settings." &
vbCr & vbCr _
& "You may need to close and reopen this workbook.", _
Buttons:=vbOKOnly + vbCritical

End Sub

I hope that helps.
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

Thanx Ron

I was hoping to avoid having to use VBA, but seems like that is the only
recourse. The data is to much for excel to handle directly. I will have to do
VBA then.

Vic

"Ron Coderre" wrote:

After I posted, I remembered that Pivot Tables based on External Data don't
allow parameters via the user interface. You'd need to use VBA to access the
CommandText of the PivotCache and write your parameters into the SQL there.

Alternatively, if practical, you could base the Pivot Table on data pulled
into one of your worksheet. Those queries allow parameters.

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

Hi Ron

Thanx. That did help. I understand the logic, but now when I try to set up a
parameter on the query, it says that parameters are not allowed. Any idea why?

Thanx

Vic

"Ron Coderre" wrote:

See if this link:
http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

I have a workbook with several sheets that has a pivot table per sheet. The
pivot tables access a sql database. The data is filtered on the period. The
filter is hardcoded into Microsoft query. All the pivot tables uses the same
filter, but very disparate data.

Is there any way that I can use a variable in the query that references
cells in the workbook?

Hi Ron

Thanks for that link. I just went there and had a browse around.
That was my old University, but when I was there microcomputers weren't
around, just a mainframe Elliott 803 .
Didn't have the luxury of an IT Services department then to help the
students, but its good to see that they provide it now.

--
Regards

Roger Govier


"Ron Coderre" wrote in message
...
See if this link:
http://www.rdg.ac.uk/ITS/info/traini...s/excel/query/

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Vic" wrote:

I have a workbook with several sheets that has a pivot table per
sheet. The
pivot tables access a sql database. The data is filtered on the
period. The
filter is hardcoded into Microsoft query. All the pivot tables uses
the same
filter, but very disparate data.

Is there any way that I can use a variable in the query that
references
cells in the workbook?