Solved: Opening a file via macro

[grichey]

Hello.
This is my first post on the forum. Thanks for the help ahead of time. I have about 20 files all named similarly with ##- myfile.xls . I am trying to get my macro to open a file where the user can enter simply say 70 or 91 which will then open either 70- myfile.xls or 91- myfile.xls. I tried as seen below to just open it with the - myfile.xls a constant and the ## as a user entered variable. This is not working

ideas?

Here where I am code wise currently:

****************
[VBA]
'Inputbox for tab to generate
Dim DivVariable As String
DivVariable = InputBox("2 Div Number", "Enter Div Number", "")
Dim FileVariable As String
FileVariable = "F:\mydir\"DivVariable"- myfile.xls"
'
'Open All Files
ChDir "F:\mydir\"
Workbooks.Open Filename:= _
FileVariable, UpdateLinks:=0

Windows(DivVariable"- myfile.xls").Activate[/VBA]
*************************************************

[jtrowbridge]

Try this:

[VBA]'Inputbox for tab to generate
Dim DivVariable As String
Dim FilePath As String
Dim FileName As String
DivVariable = InputBox("2 Div Number", "Enter Div Number", "")
FilePath = "F:\mydir\"
FileName = DivVariable & "- myfile.xls"
'
'Open All Files
Workbooks.Open FileName:=FilePath & FileName, UpdateLinks:=0
Windows(FileName).Activate[/VBA]

[Bob Phillips]

Why not just use filedialog and let them browse for it

[vba]

Dim nCount As Long

' Open the file dialog
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Show

' Display paths of each file selected
For nCount = 1 To .SelectedItems.Count
Workbooks.Open .SelectedItems(nCount)
Exit For
Next lngCount
End With
[/vba]

[Zack Barresse]

You should always check if the workbook is open before opening a file via VBA. You can do so with a simple function...

[vba]function WbOpen(wbName as string) as Boolean
'Originally found by Jake Marx
on error resume next
wbopen = len(workbooks(wbname).name)
end function[/vba]

HTH

[grichey]

Thank you for all the great suggestions. I will try them out and see which best fits the need of the project.

[grichey]

Does the code to check if it's already open go anywhere specific? Can that be right in the middle of my 'sub'. Sorry if that's a n00b question. I've only been working with VBA for a few days.

Thanks

[Bob Phillips]

Put it before the opne code, there is no point opening it if it already open.

[grichey]

Are functions like that to be within a Sub? It says that I must end the sub before having that code?

[Bob Phillips]

Yes indeed, if it isn't within a sub it couldn't be invoked/called.

[Zack Barresse]

I generally try to set books as variables when using them via code. To set a workbook to a variable dependent on whether it is open or not, what I use (just one way)...
[vba]dim wb as workbook
'...
if wbopen("name.xls") = true then set wb = workbooks("name.xls")
if wb is nothing then set wb = workbooks.open("C:\path\name.xls")
'...
msgbox wb.name 'whatever here... you can manipulate it with this variable[/vba]

HTH