Outlook Excel Compare data from two closed workbooks

Question

I am new to Outlook VBA and am trying to compare the value in the same cell from two different closed workbooks.

I have created an Excel Object upon receiving an attachment from an email that saves the attachment as a CSV file.

I then want to check that the header row within the file matches a master copy which has the same headers in the first row but I am not sure how to reference the sheet names nor the cells using the objects. I have tried many ways using VBA for Excel but it doesn't seem to work in Outlook.

If any one can assist me it would be greatly appreciated.

Function ConvertXls2CSV(sXlsFile As String)

On Error Resume Next

Dim oExcel As Object
Dim oExcelWrkBk As Object
Dim bExcelOpened As Boolean 'Was Excel already open or not
Dim OriginalFile As String
Dim MasterFile As String
Dim Fault As Integer

Set oExcel = GetObject(, "Excel.Application") 'Bind to existing instance of Excel

If Err.Number <> 0 Then 'Could not get instance of Excel, so create a new one
    Err.Clear
    On Error GoTo Error_Handler
    Set oExcel = CreateObject("excel.application")
    bExcelOpened = False
Else 'Excel was already running
    bExcelOpened = True
End If    

Set oExcelWrkBk = oExcel.Workbooks.Open(sXlsFile)
oExcelWrkBk.SaveAs Left(sXlsFile, InStrRev(sXlsFile, ".")) & "csv", xlCSVWindows


/*THIS IS WHERE I WANT TO REFERENCE THE CELLS IN THE WORKBOOKS*/

OriginalFile = oExcelWrkBk.Sheets("PK Price Data").Cells(1, 1).Value
MasterFile = oExcelWrkBk."MasterFile.xls".Sheets("PK Price Data").Cells(1, 1).Value

if OriginalFile = MasterFile then
fault = 1
else fault = 0
end if

oExcelWrkBk.Close False

If bExcelOpened = False Then
   oExcel.Quit
End If

End Function

Many Thanks Melinda

Why are you doing this is Outlook? If you are processing Excel workbooks, Excel VBA would seem more sensible. Do you not know how to loop through worksheets, rows and cells? — Tony Dallimore

Noldor130884 Noldor130884 · Accepted Answer · 2013-09-03T06:08:51

As far as I know, it's not possible to address your references to a closed workbook. You can, however, open it without showing with application.screenupdating = false. When you're done storing your references in some variables, you can simply close the workbooks and set application.screenupdating = true

Outlook Excel Compare data from two closed workbooks

1 Answers