Excel Macro Difficulties

[Aidan Gullickson]

I am having problems with an Excel macro that was originally written
on a Windows machine. This macro works completely fine on a Windows
computer, but gives back all kinds of error messages on any Mac. Here
is the full text of the macro. Does anyone have any suggestions?
Thanks.

Windows("convert NES headers.xls").Visible = False

' Generate an "Open File" window for the user to select the NES header
files

fNameArray = Application.GetOpenFilename("Text Files (*.txt),
*.txt", MultiSelect:=True)

' Loop through each file and perform a Text to Columns import

For Each fName In fNameArray

Workbooks.OpenText Filename:= _
fName, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2),
Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2))

' Delete the top "comment" row created by the first comment line in
the NES header file

Rows("1:2").Select
Selection.Delete Shift:=xlUp

' Build the spreadsheet filename from the NES header filename: take
current filename minus
' the ".txt" and replace with ".xls"

Dim newfName, position, newfNameandType
position = (fName,".txt")
newfName = Left(fName, position - 1)
newfNameandType = newfName + ".xls"

ActiveWorkbook.SaveAs Filename:=newfNameandType,
FileFormat:=xlNormal

ActiveWindow.Close

Next


MsgBox "Conversion of NES headers complete."

Windows("convert NES headers.xls").Close False

End Sub

 

[Bob Greenblatt]

I am having problems with an Excel macro that was originally written
on a Windows machine. This macro works completely fine on a Windows
computer, but gives back all kinds of error messages on any Mac. Here
is the full text of the macro. Does anyone have any suggestions?
Thanks.

Windows("convert NES headers.xls").Visible = False

' Generate an "Open File" window for the user to select the NES header
files

fNameArray = Application.GetOpenFilename("Text Files (*.txt),
*.txt", MultiSelect:=True)

' Loop through each file and perform a Text to Columns import

For Each fName In fNameArray

Workbooks.OpenText Filename:= _
fName, Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited,
TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False,
Semicolon:=False _
, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 2), _
Array(2, 2), Array(3, 2), Array(4, 2), Array(5, 2),
Array(6, 2), Array(7, 2), Array(8, 2), _
Array(9, 2))

' Delete the top "comment" row created by the first comment line in
the NES header file

Rows("1:2").Select
Selection.Delete Shift:=xlUp

' Build the spreadsheet filename from the NES header filename: take
current filename minus
' the ".txt" and replace with ".xls"

Dim newfName, position, newfNameandType
position = (fName,".txt")
newfName = Left(fName, position - 1)
newfNameandType = newfName + ".xls"

ActiveWorkbook.SaveAs Filename:=newfNameandType,
FileFormat:=xlNormal

ActiveWindow.Close

Next


MsgBox "Conversion of NES headers complete."

Windows("convert NES headers.xls").Close False

End Sub

The syntax for getopenfilename is not correct for the Macintosh. Check the
help file for the proper syntax. I think there is also a problem with
multiselect on the Macintosh, so the logic may need to be changed to look at
each file in the directory and only process the text files.

 

[Jim Gordon MVP]

Hi Aidan,

On the Mac, Multi-Select only has one correct setting, which is FALSE. If
you set it to TRUE you will get an error. Here's more info excerpted from
the help file:

On the Macintosh, this string is a list of comma-separated file type codes
(for example, "TEXT,XLA5,XLS4"). Spaces are significant and shouldn't be
inserted before or after the comma separators unless they're part of the
file type code. If omitted, this argument defaults to all file types.

FilterIndex Optional Variant. Windows only (ignored on the Macintosh).
Title Optional Variant. Windows only (ignored on the Macintosh).
ButtonText Optional Variant. Macintosh only (ignored in Windows).
Specifies the text used for the Open button in the dialog box. If this
argument is omitted, the button text is "Open."
MultiSelect The default value is False.


-Jim Gordon
Mac MVP

All responses should be made to this newsgroup within the same thread.
Thanks.

About Microsoft MVPs:
http://www.mvps.org/

Before posting a "new" topic please be sure to search Google Groups to see
if your question has already been answered.


----------