Excel sheets linked to different msquerys 1

[Cpreston]

Hi

Just wondering if this would be possible.

If we have a workbook (Excel 2010) and there are for example 3 sheets. Each needs to have 3 different Msquerys. Is it possible to run all 3 query's from one command button.

So sheet 1 would come up with between prompt dates then refresh sheet1 data
Then date prompts for sheet 2 would then come up and refresh data on sheet 2

etc.....

Is this possible at all? If so could someone point me in the right direction with the coding please

Many thanks

 

[SkipVought]

Hi,

So each sheet would contain three QueryTables, where each QT uses the same two Date Value Parameters?

I'd use two cells for the Date Parameters

To build this:
1) Set up your three queries so they return the resultsets to the sheet in accordance with your requirements.

2) The Parameters can be configured to get the value from a prompt or from two cells on your sheet.

3) Your button code would be something like this.

    For [b][highlight #FCE94F]i[/highlight][/b] = 1 To 3
        With ActiveSheet.ListObjects([b][highlight #FCE94F]i[/highlight][/b]).QueryTable
            .Refresh False
        End With
    Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

Thanks for the reply. The code you gave is giving a runtime error of 9 and on debu is highlighting the code below.

With ActiveSheet.ListObjects(i).QueryTable

I have 3 sheets called, Sales, Purchase and invoice and all have date look up on the MSquerys.

It appears to update the dates in Sales tab but then does not go any further.

Any more advice please

Thanks

 

[SkipVought]

Which sheet was active?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Sales

 

[SkipVought]

What is the value of i when you get the error and debug? Hover over the variable or insert a DebugPrint i statement in the loop.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Sorry I do not know how to do a debugprint i statement

Where can I se the value of I.

It gives run-time error '9'
Subscript out of range

When I go to debug it just highlights in Yellow
With ActiveSheet.ListObjects(i).QueryTable


Thanks

 

[SkipVought]

'
   For i = 1 To 3
        Debug.Print i
        With ActiveSheet.ListObjects(i).QueryTable
            .Refresh False
        End With
    Next

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

I really am sorry I don't seem to be able to see anything. I have the code like this

Sub test()
For i = 1 To 3
Debug.Print i
With ActiveSheet.ListObjects(i).QueryTable
.Refresh False
End With
Next
End Sub

So where does it show the I value it still goes ont eh debug back to the same line, where do I see the I value?

 

[Cpreston]

The immediate window shows this

1
2
1
2
1
2

 

[SkipVought]

View > Immediate

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

yes I fond that it just shows

1
2
1
2
1
2

 

[SkipVought]

So on the Sales sheet, do you have three separate tables that represent the three queries you have configured?

And can you select in each one and EDIT the query that you configured?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Oh No sorry they are different sheets but in the same work book

Do they have to be on the same sheet or can it be done over different work sheets

Sorry for the misunderstanding

 

[SkipVought]

You stated, "...there are for example 3 sheets. Each needs to have 3 different Msquerys."

I assumed from that statement that each or the three sheets had three different queries. as I stated in my first post. "So each sheet would contain three QueryTables, where each QT uses the same two Date Value Parameters?"

You could have corrected me at that point.

So now lets get the straight skinny.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

Yes they all use a field called deliverydate to run their results. Some other in them differs, which makes them different, but the date value parameter is the same.

They are in 3 different tabs, Sales, Purchase and Invoice. I can Refresh all but then I have to enter the 2 dates in 3 times , once for each query.

I want to be able to just enter the dates once and it populates the data using the deliverydates given.

Thanks

 

[SkipVought]

If you right-click in the Sales (first sheet) and select Table > Parameters, a Parameters window pops up with three options. The THIRD option references a cell. Reference the same cell in the Sales sheet for the querytables in all sheets.

Then modify the code...

'
   Dim sName as string
   For i = 1 To 3
        Select Case I
           Case 1: sName = "Sales"
           Case 2: sName = "Purchase"
           Case 3: sName = "Invoice"
        End Select
        With Worksheets(sName).ListObjects(1).QueryTable
            .Refresh False
        End With
    Next

Sorry, ListObjects(1) change from i to 1

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Hi

Sorry about the late response I have just tried your solution

I now have my code below (changed some Sheet names) and also changed the 3rd option in parameters (=Sales!$A$2)

Sub test()
'
Dim sName As String
For I = 1 To 3
Select Case I
Case 1: sName = "Sales"
Case 2: sName = "Sales Date req"
Case 3: sName = "Sales Invoice"
End Select
With Worksheets(sName).ListObjects(1).QueryTable
.Refresh False
End With
Next
End Sub

However, it keep asking for dates from and between for all 3 sheets and does not update them all from one prompt

Any ideas please.

Thanks

 

[SkipVought]

Did you, in all three QueryTables (on each sheet) perform the Parameter setup as specified in 8 Dec 17:16?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Cpreston]

Originally did one, but then did all 3 when it did not work. but still the same issue, it asks for 6 prompts because it is between dates