Results 1 to 8 of 8

Thread: VBA Excel macro

  1. #1
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15

    Unanswered: VBA Excel macro

    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

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  3. #3
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    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...

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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

  5. #5
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    Quote Originally Posted by DavidCoutts

    HTH

    David
    It does. Thanks David

  6. #6
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    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

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    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"

  8. #8
    Join Date
    Jun 2004
    Location
    Italy
    Posts
    15
    Ok, it works fine.
    Thanks for your help David.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •