Accessing another workbook's data

[Karthi]

Hi All,
My objective is to get used range of rows and columns of another workbook
("trial_2.xls").
I will run the following macro in "trial_1.xls" workbook.
----------------------------------------------
software configuration:
Microsoft office Excel 2003 (11.8142.8132) SP2
Microsoft Visual basic 6.3
----------------------------------------------
Function two()
Dim wbk As Excel.Workbook
Dim sh As Excel.Worksheet
Dim r As Integer
Dim c As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Set wbk =
Workbooks.Open("D:\Karthi_Works\trial\Excel_Trials\trial_2.xls", True, True)
sh = wbk.Worksheets(1)
r = sh.UsedRange.Rows.count
c = sh.UsedRange.Columns.count
MsgBox (rc)
wbk.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Function

When I run this macro in debug mode, the value of 'wbk' shows "Nothing".
Because of this, I could not display used range of rows and columns.
The line "Set wbk =
Workbooks.Open("D:\Karthi_Works\trial\Excel_Trials\trial_2.xls", True, True)"
has
some problem. I couldnot figure it out. please, help me in this regard

Thanks
Karthi

 

[Mike H]

Hi,

I changed it from a fuction to a sub to make it easier for me so change it
back. Also msgbox(rc) doesn't work because it interprets rc as a new variable
and it's never populated so I changed that:-

Sub two()
Dim wbk As Excel.Workbook
Dim sh As Excel.Worksheet
Dim r As Integer
Dim c As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
Set wbk = Workbooks.Open("c:\june.xls", True, True) 'Change back to your path
Set sh = wbk.Worksheets(1)
r = sh.UsedRange.Rows.Count
c = sh.UsedRange.Columns.Count
MsgBox ("Rows used " & r & " Columns used " & c)
wbk.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub


Mike

 

[Karthi]

Thanks a lot Mike.

The code is working fine when it is written in "sub two() ... end sub".
But, it is not working when it is written in "function two() .. end
function". Could you explain me the difference between Function and Sub in
excel VBA macro?

Thanks & Regards
Karthi