Referencing Query in VBA

[mExd2014]

Hi all

Scenario as follows;

I have a userform that is set up which will be run from any workbook (the workbook will always have a unique name). However the userform is set up to open another workbook in the background when a command button is clicked.

My problem is that I want to extract some data from the unique workbook when the command button is initialised.

Where can I put the code so that the original workbook becomes the active workbook, bearing in mind that when the userform is selected, the workbook in the background becomes the active workbook.

Hope this makes some sense.

Thanks in advance

 

[mumps]

You can place some code such as this at the beginning of your macro. It defines the variable "origWB" to refer to the original workbook which contains the macro. Then you can use that variable to refer to the original workbook and perform any action on it.

Dim origWB As Workbook
Set origWB = ThisWorkbook

 

[mExd2014]

So the macro is just stored in the personal workbook rather than a particular workbook so the userform can be initialised on any workbook.

If I include it in the command button part of the code, it will refer to this workbook as the one that opens in the background?

 

[mumps]

This c ode assumes that you have only the two workbooks open:

Dim origWB As Workbook, wb As Workbook
For Each wb In Workbooks
    If wb.Name <> ActiveWorkbook.Name Then
        Set origWB = Workbooks(wb.Name)
    End If
Next wb

 

[mExd2014]

Thank you, managed to get it to work!

 

[mumps]

You are very welcome.