VBA Excel macro

ioclio · #1 (**permalink**) 06-30-04, 08:42

Hi,

I developed an Excel macro using XP (Office 10.0 Object Library).
Now the users are asking to run it with previous Excel versions (Excel 95, 97).
Now the problem is that I used Application.FileDialog(msoFileDialogFilePicker) to browse for files and I don't know how this could be replaced with older libraries (actually I could use a simple InputBox, but then I discovered this forum...

).

Thanks in advance
FatherXmas

DavidCoutts · #2 (**permalink**) 06-30-04, 11:36

HI

If its for opening or saving files etc
I know for XL97 you can use

Application.GetOpenFilename

which return the Path and Name of the selected item and you can use
Store it to a variable
the workbookname you can get from using Left and Right Functions

Code:

    myFile = Application.GetOpenFilename
    Filename = Right(myFile, Len(myFile) - InStrRev(myFile, "\"))
    Filename = Left(Filename, InStr(Filename, ".") - 1)

or using ActiveWorkbook.Name if you open it.

the equivalent for the Save Dialog is
Application.GetSaveAsFilename

Im Afraid i dont know about XL95
Hope this Helps

David

ioclio · #3 (**permalink**) 06-30-04, 11:57

Hi David,
thanks for help,that's actually a good hint. The fact is that I should allow multiple selections...something like:

Dim FileDlg As FileDialog
Set FileDlg = Application.FileDialog(msoFileDialogFilePicker)
Do
FileDlg.Title = "Select which files to use"
FileDlg.InitialView = msoFileDialogViewList
FileDlg.InitialFileName = "C:\"
FileDlg.Show
NumberOfSelections = FileDlg.SelectedItems.Count

If NumberOfSelections = 0 Then
Selection = MsgBox("No file selected." & vbCrLf & _
"Stop?", vbYesNo)
If Selection = vbYes Then
Exit Sub
End If
End If
Loop While (NumberOfSelections = 0)

I think Application.GetOpenFilename let you select only one file...

DavidCoutts · #4 (**permalink**) 06-30-04, 15:55

Hi

you can actually select multiplefiles with getopenfilename

something like

Code:

    Dim aryOpenFiles As Variant, i As Integer
    aryOpenFiles = Application.GetOpenFilename(MultiSelect:=True)
    If IsArray(aryOpenFiles) Then
        For i = LBound(aryOpenFiles) To UBound(aryOpenFiles)
            'insert your code here
            MsgBox aryOpenFiles(i)
        Next i
    End If

i threw in a messagebox but put your own code in there

HTH

David

ioclio · #5 (**permalink**) 07-01-04, 03:18

Quote:

Originally Posted by DavidCoutts

HTH

David

It does. Thanks David

ioclio · #6 (**permalink**) 07-01-04, 03:47

Oopss...just another question:

how can I dinamically let the user browse for a directory starting from a defined path?
I mean...just suppose someone opens a.xls in C:\whoknows\.
How can I make the browser starting from C:\whoknows\ ?

Thanks again

DavidCoutts · #7 (**permalink**) 07-01-04, 10:26

Before you make a call to the application.getopenfilename
just change the directory to what you want,
I they have opened a previous example use the LEFT function to get the Path store it as a variable(myFilePath) then use

ChDir MyFilePath

or just force them to some place like

ChDir "C:\WhoKnows"

ioclio · #8 (**permalink**) 07-01-04, 11:02

Ok, it works fine.
Thanks for your help David.

Internet Services

Web Development

Digital Marketing

Consumer Tech