当前位置: 动力学知识库 > 问答 > 编程问答 >

database - Reference a cell in another workbook. And make my query not refresh if values are equal

问题描述:

It says I have too few parameters expected 15... It is something with my before and after refresh.

Class 1

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)

Application.Worksheets("RawDataLines").Range("A1") = Application.Worksheets("RawDataLines").Range("C1")

Application.Run "'Operation Get Ipads.xls'!Assembly1_Button"

End Sub

Class 2

Public WithEvents qut As QueryTable

Private Sub qut_BeforeRefresh(Cancel As Boolean)

Worksheets("RawDataLines").Range("C1") = _

"='H:\Departments\Manufacturing\Production Links\DashBoard Breakdown\[MASTER_LIVE_STATUS_DATA.xls]Sheet1'!R1C1"

If Application.Worksheets("RawDataLines").Range("C1") = Application.Worksheets("RawDataLines").Range("A1") Then

Cancel = True

End If

End Sub

Initialize:

Dim T As New Class1

Dim H As New Class2

Sub Initialize_It()

Set T.qt = ThisWorkbook.Sheets(3).QueryTables(1)

Set H.qut = ThisWorkbook.Sheets(3).QueryTables(1)

End Sub

网友答案:
Private Sub qt_BeforeRefresh(ByVal Success As Boolean)

the argument is usually Cancel and this is what your code refers to. Change it to Cancel.

You can refer to the current workbook (where this code is running) as ThisWorkbook. Assuming it is the MASTER one, then you can use:

ThisWorkbook.Worksheets("sheet1").Range("A1")

The Workbooks collection refers only to open workbooks, so you will need to temporarily open the other workbook:

Dim wbOther As Workbook

Set wbOther = Workbooks.Open("full path and filename.xlsx", False)

the False argument indicates that you do not wish to Update Links when opening the book (if appropriate).

When you have finished with the other book, use:

wb.Close False    'False says that you do not need to Save Changes
Set wb = Nothing

It is possible to get a single value from another workbook without opening and closing it, but if that book also has links this may cause an issue:

Debug.Print ExecuteExcel4Macro("'F:\Documents and Settings\student\My Documents\[AndysData7.xlsx]Staff List'!R6C4")

Note, the formula needs to use R1C1 notation. (Rather than Debug.Print you would store the value in a variable.) Note also that I am not necessarily recommending this approach, as it is undocumented, but thought I'd mention it in the context of the question.

分享给朋友:
您可能感兴趣的文章:
随机阅读: