Hi!
I have 2 files where I'm trying to link data together using VB and Excel. One is a text file and the other is a DIF (data interchange format) file that I open in Excel. For each row in the text file I need to do a lookup in the DIF file and then get data from that row.
At first I tried to do this with a simple nested loop, but it was far too slow. So now I'm trying to call Excel's MATCH function from VB, but I'm running into the following error:
Error 1004: Unable to get the Match property of the Worksheetfunction class.
Here is the section of code. I'm trying to update a cell in the text file with a value from the DIF file where two values are matching:
With appExcel
.Workbooks(G_FILE1).Worksheets(1).Cells(intRowCnt, 6) _
= .Application.WorksheetFunction.Match _
(strEmpNum, .Workbooks(G_FILE2).Worksheets(1).Range _
("A:A"
, 0)
Any idea what I'm doing wrong? The code (aside from the extra Workbook references) works when run from Excel VBA, but I need to distribute this as part of a VB app. Is there a more efficient solution to this problem? Normally I'd just work directly with the text files, but the formatting of the DIF file when opened as text has thrown me off a bit. Thanks!
PTW
I have 2 files where I'm trying to link data together using VB and Excel. One is a text file and the other is a DIF (data interchange format) file that I open in Excel. For each row in the text file I need to do a lookup in the DIF file and then get data from that row.
At first I tried to do this with a simple nested loop, but it was far too slow. So now I'm trying to call Excel's MATCH function from VB, but I'm running into the following error:
Error 1004: Unable to get the Match property of the Worksheetfunction class.
Here is the section of code. I'm trying to update a cell in the text file with a value from the DIF file where two values are matching:
With appExcel
.Workbooks(G_FILE1).Worksheets(1).Cells(intRowCnt, 6) _
= .Application.WorksheetFunction.Match _
(strEmpNum, .Workbooks(G_FILE2).Worksheets(1).Range _
("A:A"
, 0)Any idea what I'm doing wrong? The code (aside from the extra Workbook references) works when run from Excel VBA, but I need to distribute this as part of a VB app. Is there a more efficient solution to this problem? Normally I'd just work directly with the text files, but the formatting of the DIF file when opened as text has thrown me off a bit. Thanks!
PTW
