Issue Accressing current range of a closed workbook

[LABKHAND]

Hi all,

I have the following code:

Private Function GetCurrentRegion(ByVal sSheetName As String) As Range
Dim R1 As Range
On Error Resume Next

Set MenuSheet = ThisWorkbook.Sheets(sSheetName)
Set R1 = Worksheets(sSheetName).Cells(1, 1).CurrentRegion

If (R1 Is Nothing) Then
MsgBox "Current Region is Empty! Please validate menu definition
metadata.", vbOKOnly + vbCritical, "MenuMkr - GetCurrentRegion"
End
End If

If R1.Rows.Count <= 1 Then
MsgBox "Not enough menu metadata is available!", vbOKOnly + vbCritical,
"MenuMkr - GetCurrentRegion"
End
End If

Set GetCurrentRegion = R1
End Function

*************************************
ISSUE:

my issue is this line:

Set MenuSheet = ThisWorkbook.Sheets(sSheetName)

This code works fine if I am accessing the passed sheet's name (sSheetName)
in an OPEN workbook. However, I need to change this line so that I can
access the passed sheet's name in a CLOSED workbook. I am having a tough
time figuring it out. This code is part of my custom add-in module. I then
call this add-in module from another workbook to do some custom processing.
How do I change this line of code?

Thanks for all your help.

 

[Jim Cone]

Open the workbook, with ScreenUpdating set to False.
Get your data and then close the workbook.
--
Jim Cone
Portland, Oregon USA




"LABKHAND"
<[email protected]>
wrote in message
Hi all,
I have the following code:

Private Function GetCurrentRegion(ByVal sSheetName As String) As Range
Dim R1 As Range
On Error Resume Next

Set MenuSheet = ThisWorkbook.Sheets(sSheetName)
Set R1 = Worksheets(sSheetName).Cells(1, 1).CurrentRegion

If (R1 Is Nothing) Then
MsgBox "Current Region is Empty! Please validate menu definition
metadata.", vbOKOnly + vbCritical, "MenuMkr - GetCurrentRegion"
End
End If

If R1.Rows.Count <= 1 Then
MsgBox "Not enough menu metadata is available!", vbOKOnly + vbCritical,
"MenuMkr - GetCurrentRegion"
End
End If

Set GetCurrentRegion = R1
End Function

*************************************
ISSUE:

my issue is this line:

Set MenuSheet = ThisWorkbook.Sheets(sSheetName)

This code works fine if I am accessing the passed sheet's name (sSheetName)
in an OPEN workbook. However, I need to change this line so that I can
access the passed sheet's name in a CLOSED workbook. I am having a tough
time figuring it out. This code is part of my custom add-in module. I then
call this add-in module from another workbook to do some custom processing.
How do I change this line of code?
Thanks for all your help.

 

[LABKHAND]

Jim,

I am not sure if that is a good option. This is an ADD-IN module. So
rather than opening the workbook, there should be a better way. Any one
else has any solution?

Thx