File Opening Macro

[Jack]

Hi everyone. I have written some code below that works after much trial and error but it worries me that I don't understand why. This code is to allow the user to select the right .xls file to open from a defined folder, first test if the chosen file is already open and then either open it or ignore opening it if already is open so a copy and paste routine (which I haven't bothered showing below) can then proceed.


The problems I had were:


1) I couldn't work out whether vWB which is the result of the GetOpenFilename should be a string or a workbook so I gave up and made it a variant - should it have been declared as a workbook or a string or is it both during the routine and so variant is right?


2) Testing if the workbook was already open I think I got this bit from a Chip Pearson post somewhere - I don't get why it's not wWB.Fullname = vWB.Fullname but through trial and error it worked if I drop the .Fullname from vWB. I guess that means vWB is not actually an object which I am confused about as I thought it was formed by the use of the set command a little earlier on and as its a variant it can be an object if need be.


thanks

John


Sub ImportStaffHrsEntryData()

On Error Resume Next

Dim sWBPath As String

Dim wWB As Workbook

Dim vWB As Variant

Dim IsWBOpen As Boolean

Dim pw As String

Worksheets("CONTROL").Activate

sWBPath = Range("SourceWBPath")

ChDrive "S"

ChDir sWBPath

vWB = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")


If vWB = False Then

' They pressed Cancel

MsgBox "Stopping because you did not select a file or pressed cancel"

Exit Sub

Else

Set vWB = Workbooks(vWB)

For Each wWB In Workbooks

If wWB.FullName = vWB Then


IsWBOpen = True

Exit For

End If

Next wWB


If IsWBOpen = False Then


Workbooks.Open vWB


End If


End If


'rest of code

end sub

 

[Luke M]

1) The GetOpenFile name returns a string. Either the full name/address of the workbook, or False. So, you could have defined this as a string, except for...


2) You later set vWB to a workbook. However, I don't think you really needed to. Since you already have the full name from the GetOpenFile, you should be able to just do this:

[pre]

'...
If vWB = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file or pressed cancel"
Exit Sub
Else

For Each wWB In Workbooks
If wWB.FullName = vWB Then
IsWBOpen = True
Exit For
End If
Next wWB
'...

[/pre]

So, I think you can declare vWB as a string. Oh, in answer to why vWB.FullName gave you an error, it's that little "s" here: Workbooks(vWB)

You defined vWB as a collection of workbooks, not a single workbook. =P

Thus, it was messing things up.

 

[Jack]

Hello Luke thanks very much for this, that really answered all my questions, I really appreciate this. I guess the fact Getopenfilename is a string not an Object was the start of my confusion. I assumed the opposite but now I look at help it says it very clearly.


Thanks again Luke thanks for taking the time to look at it.

John

 

[Jack]

ps Luke I just made the changes you suggested and the the interesting thing is vWB does have to be declared as Variant (I tried string which didn't work it just = False) for the routine to work. I think the reason is the Getopenfilename result needs Variant as the result can be FALSE so I am guessing that it has to be a variant to hold a string or Boolean? Just a guess. The problem I find is the VBA help examples often don't show the variable declarations needed. Anyway all good, I leave the vWB as variant, delete the Set line as you said and all works great now and I understand it better for next time.


thanks again

 

[asa]

Hi John,


That's true about the example in the Help system, but notice that the help system specifies "Return Value" early on in the help entries. In this case, it is specified as Variant there.


Asa

 

[Jack]

Thanks asa, being a male I must admit I don't 'read the instructions' until I am forced to kicking and screaming! I better improve on this attitude as I am wasting too much time on not getting variables declared correctly then it compounds further down the code as you do other things trial and error to try and get it to work. I think the other time waster for me is to learn how to use the Object Model more effectively as well as when I started with VBA this just made no sense to me. I will google on this topic and try and get a better handle on the Object Model.