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

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