Programmatic indication of file to be PROCESSED.
 

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

Programmatic indication of file to be PROCESSED.
 

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

Programmatic indication of file to be PROCESSED.
 

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)

Programmatic indication of file to be PROCESSED.
 

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)

Programmatic indication of file to be PROCESSED.
 

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

Programmatic indication of file to be PROCESSED.
 

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

Programmatic indication of file to be PROCESSED.
 

Hi Hari,

Sorry about that. I had adapted some code I used with worksheets, and did
not fully change it it.

Change the line

Set CurrentSheet = ActiveWorkbook.Worksheets(i)

to

Set CurrentSheet = ActiveWorkbook.ActiveSheet

and it should be okay.


--

HTH

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


"Hari Prasadh" wrote in message
...
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

Programmatic indication of file to be PROCESSED.
 

Hi Bob,

Is it possible for this code to be modified from a check-box to a Radio
control kind of box where only one workbook could be chosen.

Also, presently your code gives an option to choose personal.xls as well.
Could that be omitted from the list.

Lastly (hope am not overreaching myself...) may be u could post this code in
ur website the way sum-product is there. I think this is also a nice tool
which many people could use/integrate in their code.

Thanks a lot,
Hari
India


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

Sorry about that. I had adapted some code I used with worksheets, and did
not fully change it it.

Change the line

Set CurrentSheet = ActiveWorkbook.Worksheets(i)

to

Set CurrentSheet = ActiveWorkbook.ActiveSheet

and it should be okay.


--

HTH

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


"Hari Prasadh" wrote in message
...
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

Programmatic indication of file to be PROCESSED.
 

Hari,

It would not be possible to change from a checkbox to optionbuttons. I agree
with you that buttons are nicer, but the code is using a dialog, and thus
once the dialog is shown my code does not get a look in until a button is
pressed. And as optionbuttons allow more than 1 at a time to be clicked, by
the time my code knows there could be any number clicked. To achieve what
you suggest would need a custom userform I think.

As to Personal.xls, you could do it one of two ways, you could check
specifically for that workbook, in the loop that sets it up, like so

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.ActiveSheet
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

If LCase(Application.Workbooks(i).Name) < "personal.xls" Then
iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = _
Application.Workbooks(i).Name
TopPos = TopPos + 13
End If

Next i

or, and this would be my chosen way, ignore all hidden workbooks, like so

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.ActiveSheet
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

If Application.Windows(Application.Workbooks(i).Name) .Visible
Then
iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = _
Application.Workbooks(i).Name
TopPos = TopPos + 13
End If

Next i


--

HTH

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


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

Is it possible for this code to be modified from a check-box to a Radio
control kind of box where only one workbook could be chosen.

Also, presently your code gives an option to choose personal.xls as well.
Could that be omitted from the list.

Lastly (hope am not overreaching myself...) may be u could post this code
in
ur website the way sum-product is there. I think this is also a nice tool
which many people could use/integrate in their code.

Thanks a lot,
Hari
India


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

Sorry about that. I had adapted some code I used with worksheets, and
did
not fully change it it.

Change the line

Set CurrentSheet = ActiveWorkbook.Worksheets(i)

to

Set CurrentSheet = ActiveWorkbook.ActiveSheet

and it should be okay.


--

HTH

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


"Hari Prasadh" wrote in message
...
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

Programmatic indication of file to be PROCESSED.
 

Hari,

Cracking up. Of course it could use single option buttons. Here is a revised
version, also ignoring hidden workbooks

Option Explicit

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 ob As OptionButton

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.ActiveSheet
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

If Windows(Workbooks(i).Name).Visible Then
iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).Caption = _
Application.Workbooks(i).Name
TopPos = TopPos + 13
End If

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 ob In thisDlg.OptionButtons
If ob.Value = xlOn Then
GetWorkbook = ob.Caption
Exit For
End If
Next ob
Else
GetWorkbook = ""
End If
Application.DisplayAlerts = False

.Delete

End With

End Function







--

HTH

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


"Bob Phillips" wrote in message
...
Hari,

It would not be possible to change from a checkbox to optionbuttons. I
agree
with you that buttons are nicer, but the code is using a dialog, and thus
once the dialog is shown my code does not get a look in until a button is
pressed. And as optionbuttons allow more than 1 at a time to be clicked,
by
the time my code knows there could be any number clicked. To achieve what
you suggest would need a custom userform I think.

As to Personal.xls, you could do it one of two ways, you could check
specifically for that workbook, in the loop that sets it up, like so

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.ActiveSheet
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

If LCase(Application.Workbooks(i).Name) < "personal.xls" Then
iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = _
Application.Workbooks(i).Name
TopPos = TopPos + 13
End If

Next i

or, and this would be my chosen way, ignore all hidden workbooks, like so

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.ActiveSheet
cLetters = Len(Application.Workbooks(i).Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If

If Application.Windows(Application.Workbooks(i).Name) .Visible
Then
iBooks = iBooks + 1
.CheckBoxes.Add cLeft, TopPos, cLetters * nWidth, 16.5
.CheckBoxes(iBooks).Caption = _
Application.Workbooks(i).Name
TopPos = TopPos + 13
End If

Next i


--

HTH

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


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

Is it possible for this code to be modified from a check-box to a Radio
control kind of box where only one workbook could be chosen.

Also, presently your code gives an option to choose personal.xls as
well.
Could that be omitted from the list.

Lastly (hope am not overreaching myself...) may be u could post this
code
in
ur website the way sum-product is there. I think this is also a nice
tool
which many people could use/integrate in their code.

Thanks a lot,
Hari
India


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

Sorry about that. I had adapted some code I used with worksheets, and
did
not fully change it it.

Change the line

Set CurrentSheet = ActiveWorkbook.Worksheets(i)

to

Set CurrentSheet = ActiveWorkbook.ActiveSheet

and it should be okay.


--

HTH

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


"Hari Prasadh" wrote in message
...
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

Programmatic indication of file to be PROCESSED.
 

Hi Bob,

Thnx a TON. It works very nicely. Would integrate it with some of the other
macros I have.

Thanks a lot,
Hari
India

Programmatic indication of file to be PROCESSED.
 

BTW, you might want to take a look at
http://www.xldynamic.com/source/xld.CDT.html

--

HTH

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


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

Thnx a TON. It works very nicely. Would integrate it with some of the
other
macros I have.

Thanks a lot,
Hari
India