Hi,

I want user to have the ability to choose a workbook for processing. Now
there are 2 possibilities.

a) Workbook is not open in the present excel session - In that case I can
use GetOpenFileName method for opening the desired file and continue with
Macro operation.

b) Workbook is ALREADY open in the present excel session - In that case how
to offer the user the facility of indicating that so and so workbook which
is already open should be used for continuing the macro operation.

And how to interweave the above 2 methods. I thought of the following which
am pasting here. If there is a more efficient method please tell me. (Also
in my code the, macro goes in to debug mode if user writes nothing in the
input box but presses a ok. How to take care of that? Also is there anything
else I should be wary of while doing this?) Please guide me.

Option Explicit

Sub FiletobeProcessed()

Dim i As Byte
Dim p As String

i = 0

Do

i = Application.InputBox("If the file to be UPCODED is not open then
write 1 else write 2", "File open or not?")

If i = 1 Then

p = Application.GetOpenFilename("XLS files (*.xls),*.xls")
Workbooks.Open (p)

ElseIf i = 2 Then

' code for condition b). I dont know how to put this code. I would like
to get the _
name of the file which is already open so that I can integrate it with
other Macros. _
A standard Input Box might not work as user might make a Typo in
writing the name _
of the file and another its difficult to write long file names.

End If

Loop While Not (i = 1 Or i = 2)

End Sub

Thanks a lot,
Hari
India

Morning Hari (it is here ;-))

I would check if it is open, then proceed, else offer the dialog

If Not IsOpen("myWorkbook.xls") Then
'do the GetFileOPen
'and open it
End If

'rest of code

Function IsOpen(FileName As String) As Boolean
On Error Resume Next
IsOpen = CBool(Len(Workbooks(FileName).Name))
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari Prasadh" wrote in message
...
Hi,

I want user to have the ability to choose a workbook for processing. Now
there are 2 possibilities.

a) Workbook is not open in the present excel session - In that case I can
use GetOpenFileName method for opening the desired file and continue with
Macro operation.

b) Workbook is ALREADY open in the present excel session - In that case
how
to offer the user the facility of indicating that so and so workbook which
is already open should be used for continuing the macro operation.

And how to interweave the above 2 methods. I thought of the following
which
am pasting here. If there is a more efficient method please tell me. (Also
in my code the, macro goes in to debug mode if user writes nothing in the
input box but presses a ok. How to take care of that? Also is there
anything
else I should be wary of while doing this?) Please guide me.

Option Explicit

Sub FiletobeProcessed()

Dim i As Byte
Dim p As String

i = 0

Do

i = Application.InputBox("If the file to be UPCODED is not open then
write 1 else write 2", "File open or not?")

If i = 1 Then

p = Application.GetOpenFilename("XLS files (*.xls),*.xls")
Workbooks.Open (p)

ElseIf i = 2 Then

' code for condition b). I dont know how to put this code. I would
like
to get the _
name of the file which is already open so that I can integrate it
with
other Macros. _
A standard Input Box might not work as user might make a Typo in
writing the name _
of the file and another its difficult to write long file names.

End If

Loop While Not (i = 1 Or i = 2)

End Sub

Thanks a lot,
Hari
India

Hi Bob,

A very Good Morning (I believe it would be closer to Good Afternoon in ur
place- .. ur mail address says UK)

Your code solves most of my problem in part b).

Only one.. The name of the file -- myWorkbook.xls -- would or might change
and during code execution time there might be many open files. So, How to
give user the facility to indicate that the file which is open is
("myWorkbook.xls"). It's like user is given the list of files which are
already open and s/he puts a tick mark against the file to be processed and
further processing is done based on that file or some similar solution.


Thanks a lot,
Hari
India


"Bob Phillips" wrote in message
...
Morning Hari (it is here ;-))

I would check if it is open, then proceed, else offer the dialog

If Not IsOpen("myWorkbook.xls") Then
'do the GetFileOPen
'and open it
End If

'rest of code

Function IsOpen(FileName As String) As Boolean
On Error Resume Next
IsOpen = CBool(Len(Workbooks(FileName).Name))
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

Good afternoon Hari,

How about this

Sub Hari()
Dim sFilename

sFilename = GetWorkbook
If sFilename < "" Then
Workbooks(sFilename).Activate
Else
sFilename = Application.GetOpenFilename("Excel Files (*.xls),
*.xls")
If sFilename < False Then
Workbooks.Open Filename:=sFilename
End If
End If

End Sub

Function GetWorkbook()
Const nPerColumn As Long = 35 'number of items per* column
Const nWidth As Long = 7 'width of each lette*r
Const nHeight As Long = 18 'height of each row
Const sID As String = "___WorkbookSelect" 'name of dialog shee*t
Const kCaption As String = " Select workbook to open"
'dialog caption


Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cLeft As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim iLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox

Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Function
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For i = 1 To Application.Workbooks.Count

If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If

Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = _
Application.Workbooks(iBooks).Name
TopPos = TopPos + 13

Next i

.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.CheckBoxes
If cb.Value = xlOn Then
GetWorkbook = cb.Caption
Exit For
End If
Next cb
Else
GetWorkbook = ""
End If
Application.DisplayAlerts = False

.Delete

End With

End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari Prasadh" wrote in message
...
Hi Bob,

A very Good Morning (I believe it would be closer to Good Afternoon in ur
place- .. ur mail address says UK)

Your code solves most of my problem in part b).

Only one.. The name of the file -- myWorkbook.xls -- would or might change
and during code execution time there might be many open files. So, How to
give user the facility to indicate that the file which is open is
("myWorkbook.xls"). It's like user is given the list of files which are
already open and s/he puts a tick mark against the file to be processed
and
further processing is done based on that file or some similar solution.


Thanks a lot,
Hari
India


"Bob Phillips" wrote in message
...
Morning Hari (it is here ;-))

I would check if it is open, then proceed, else offer the dialog

If Not IsOpen("myWorkbook.xls") Then
'do the GetFileOPen
'and open it
End If

'rest of code

Function IsOpen(FileName As String) As Boolean
On Error Resume Next
IsOpen = CBool(Len(Workbooks(FileName).Name))
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)

Hi Bob,

It's a fantastic piece of code. Didn't know that it would involve so much of
abstruse coding . Time for me to get started with buttons, checkboxes etc.

Thnx a TON.

Regards,
Hari
India

Hi Bob,

Just a..

If I have 2 workbooks open (excluding personal.xls). One of the workbooks
have 8 sheets while the other one has 3. Both these contain data. I create a
new workbook by pressing Ctrl +N and if I run the macro then the macro
breaks at -- Set CurrentSheet = ActiveWorkbook.Worksheets(i) -- by
generating a -- Run time error '9' subscript error out of range.

If I comment the above statement and re-run the macro then it doesnt break.

And since a new workbook has 3 worksheets by default (as set in my system)
so in the macro when I hover my mouse over the value of i, it shows as 4. In
Immediate window if I type -- ?activeworkbook.name -- I get Book 1 -- and if
I type -- ?activesheet.name-- I get Sheet 1. Now, Book1 is the newly
created workbook (Ctrl +N) just before the running of the macro. Your code
is trying to access the fourth worksheet (i=4) within that workbook ?

Thanks a lot,
Hari
India