How to open and read a xls file within VBA code? 2

[rock31]

Dear there,
I need to open a xls file within my VBA code such as

sub readxlsdata()
' open xx.xls
' read 4 cells from sheet 1

end sub

I wonder, I should I do this?

Thanks alot.
R

 

[rmikesmith]

R,

Here is some code to do what you want:

Sub readxlsdata()
Dim wkbData As Workbook
Dim a, b, c, d

  Set wkbData = Workbooks.Open("C:\FilePath\xx.xls")
  With wkbData.Worksheets("Sheet1")
    a = .Range("A1").Value
    b = .Range("A2").Value
    c = .Range("A3").Value
    d = .Range("A4").Value
  End With
  
End Sub

Of course, the ranges are just examples and you'll replace these with your own. Also note, the C:\FilePath\ should be replaced with the actual directory path to your xls file. I did not include error handling but you should, especially when opening files.


Regards,
Mike

 

[firefytr]

It's always best to test if the file is already open or not, which we can do with a simple function ...

Sub TTtest()
    Dim wb As Workbook, blnWasOpen As Boolean
    Dim a, b, c, d
    If IsWbOpen("Book1.xls") Then
        Set wb = Workbooks("Book1.xls")
        blnWasOpen = True
    Else
        Set wb = Workbooks.Open("C:\YourPathHere\Book1.xls")
        blnWasOpen = False
    End If
    With wb.Sheets("Sheet1")
        a = .Range("A1").Value
        b = .Range("A2").Value
        c = .Range("A3").Value
        d = .Range("A4").Value
    End With
    If Not blnWasOpen Then wb.Close savechanges:=True
End Sub

Function IsWbOpen(wbName As String) As Boolean
    On Error Resume Next
    IsWbOpen = Len(Workbooks(wbName).Name)
End Function

HTH

-----------
Regards,
Zack Barresse

 

[rock31]

Thank you firefytr, and rmikesmith.
What you posted are of great help to me.
R.