File Open Multiselect

[scantor145]

Excel 2003 with Visual Basic 6.3

The portion of the macro below adds sheets, if necessary, to a new workbook, enables the user to select the appropriate files, then names the sheets accordingly.

Let's say, for example, that the names of the files are the following:

a.xml
b.xml
c.xml

When I get to the part of the macro that allows the files to be chosen from the File Open Dialog box, I use the CONTROL key to select each file, one at a time. I choose the files in the order, a.xml, b.xml and c.xml.

My question is why does the name of the first sheet turn out to be the last file that I've chosen, that is, c.xml.

The order of the sheets is c.xml, a.xml, b.xml.

In other words, no matter how many files I choose to process, the last file chosen is the first one processed.

I want them processed in the order I selected them, a,b,c, etc.

Please Login or Register  to view this content.

[Dave Peterson]

I don't think you can control that behavior.

You could loop through the File|open dialog and just get one name at a time
(over and over and over until the user doesn't choose one).

Or maybe you could sort the names after you've retrieved them (or were those
just sample names in your post??)

scantor145 wrote:
>
> Excel 2003 with Visual Basic 6.3
>
> The portion of the macro below adds sheets, if necessary, to a new
> workbook, enables the user to select the appropriate files, then names
> the sheets accordingly.
>
> Let's say, for example, that the names of the files are the following:
>
> a.xml
> b.xml
> c.xml
>
> When I get to the part of the macro that allows the files to be chosen
> from the File Open Dialog box, I use the CONTROL key to select each
> file, one at a time. I choose the files in the order, a.xml, b.xml and
> c.xml.
>
> My question is why does the name of the first sheet turn out to be the
> last file that I've chosen, that is, c.xml.
>
> The order of the sheets is c.xml, a.xml, b.xml.
>
> In other words, no matter how many files I choose to process, the last
> file chosen is the first one processed.
>
> I want them processed in the order I selected them, a,b,c, etc.
>
> Code:
> --------------------
> Workbooks.Add
>
> NumFiles = InputBox("Enter number of files" & Chr(13) & Chr(13) & "Select ALL files in dialog box" & Chr(13) & Chr(13) & "When File|Open dialog box is displayed," & Chr(13) & "Click, Ctrl-Click or Shift-Click to select files", "Files", "", 3500, 3000)
>
> For N = 1 To NumFiles - 3
> Sheets.Add
> Next N
>
> Application.DisplayAlerts = False
>
> If NumFiles = 2 Then
> Sheets(1).Select
> ActiveWindow.SelectedSheets.Delete
> End If
>
> If NumFiles = 1 Then
> Sheets(Array("Sheet2", "Sheet3")).Select
> ActiveWindow.SelectedSheets.Delete
> End If
>
> FilterList = "XML Files(*.xml),*.xml"
>
> With Application
> MyFile = .GetOpenFilename(filefilter:=FilterList, MultiSelect:=True)
> End With
>
> For SheetNumber = LBound(MyFile) To UBound(MyFile)
>
> If UBound(MyFile) <> NumFiles Then
> Press = MsgBox("You didn't select the correct number of files." & Chr(13) & Chr(13) & "Restart macro and try again.", vbCritical)
> ActiveWorkbook.Close
> End
> End If
>
> MyFileLen = Len(MyFile(SheetNumber))
> FoundChar = 0
> For I = MyFileLen - 4 To 1 Step -1
> If Mid(MyFile(SheetNumber), I, 1) = "\" Then
> FoundChar = I
> GoTo DoneFindingFileName
> End If
> Next I
>
> DoneFindingFileName:
> Sheets(SheetNumber).Select
> ActiveWindow.Zoom = 80
> Sheets(SheetNumber).Name = Mid(MyFile(SheetNumber), FoundChar + 1, MyFileLen - FoundChar - 4)
> --------------------
>
> --
> scantor145
> ------------------------------------------------------------------------
> scantor145's Profile: http://www.excelforum.com/member.php...o&userid=14766
> View this thread: http://www.excelforum.com/showthread...hreadid=469491

--

Dave Peterson