Excel 2002 using Visual Basic 6.3

Two files are attached in a zip file that would make it easier for me
to explain my problems.

File #1: Absorbances1.xml
File #2: Copy of Absorbances1.xls

Question #1:
When opening Absorbances.xml in Excel 2002 (File|Open), the file that
results is Copy of Absorbances1.xls. The file actually opens as an xml
[Read-Only] file. I then saved it as an xls file. Notice that every
column of data is duplicated. Anybody know why that occurs and if it
can be prevented?

Question #2:
Below is a portion of a macro that utilizes the xml file noted above.


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)

Workbooks.Open Filename:=MyFile(SheetNumber)
--------------------


It's not very complicated, but I'll try and explain what's happening or
not happening as the case may be.

Before that I would like to find out the following: In VB6 when I
stepped line by line through a macro , if I moved the cursor over a
variable, the value would appear, similar to a ToolTip display. For
some reason I don't see that when stepping through a macro in VB6.3. I
turned the ToolTip option on, but that doesn't seem to work. Any
ideas?

Now, for questions concerning the macro.
Briefly, I add a new workbook and ask how many files will be processed.
I then add/delete the appropriate number of sheets to the workbook. A
typical workbook displays 3 sheets, so if I'm only processing 2 files
then I only need 2 sheets. I then proceed to a File|Open dialog box
and select the appropriate number of files. For example, I may choose
2 of the xml files. The SheetNumber variable is then set by
LBound(MyFile), =1, and UBound(MyFile), =2. If the wrong number of
files are selected, then I start over. The next lines are just a way
for me to extract the name I wish to give to each sheet, from the
chosen filename. For eample, the first sheet woulbe called
Absorbances1. Now, finally the problem. When I get to


Code:
--------------------
Workbooks.Open Filename:=MyFile(SheetNumber)
--------------------


The file does open, and yields the file that I called File #2 above.
The name of the sheet is as it's supposed to be, namely Absorbances1,
but as soon as the file is opened the other sheet(s) in the workbook
disappear. Remember, I chose 2 files which lead to the creation of a
workbook with 2 sheets. The second sheet vanishes. Therefore, after
the rest of the macro, not shown, is processed, it comes back with a
"subscript out of range" message. That's because it's looking for a
sheet, that is not there. Why did it vanish and is there anything I
can do?


+-------------------------------------------------------------------+
|Filename: XML Issues.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3528 |
+-------------------------------------------------------------------+

--
scantor145
------------------------------------------------------------------------
scantor145's Profile: http://www.excelforum.com/member.php...o&userid=14766
View this thread: http://www.excelforum.com/showthread...hreadid=381571