PDA

View Full Version : Solved: Opening a file via macro



grichey
10-19-2007, 12:15 PM
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:

****************

'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
*************************************************

jtrowbridge
10-19-2007, 01:37 PM
Try this:

'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

Bob Phillips
10-19-2007, 03:49 PM
Why not just use filedialog and let them browse for it



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

Zack Barresse
10-19-2007, 09:14 PM
You should always check if the workbook is open before opening a file via VBA. You can do so with a simple function...

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

HTH

grichey
10-22-2007, 07:25 AM
Thank you for all the great suggestions. I will try them out and see which best fits the need of the project.

grichey
10-22-2007, 08:49 AM
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
10-22-2007, 08:54 AM
Put it before the opne code, there is no point opening it if it already open.

grichey
10-22-2007, 10:11 AM
Are functions like that to be within a Sub? It says that I must end the sub before having that code?

Bob Phillips
10-22-2007, 10:45 AM
Yes indeed, if it isn't within a sub it couldn't be invoked/called.

Zack Barresse
10-24-2007, 07:08 AM
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)...
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

HTH