External File Handling Code Help Needed

[JLatham]

I need help helping someone. They are using Excel on a Mac and apparently I
know even less than I thought I did about programming in VBA on that
platform (which was pretty much nothing - no experience with it at all).

Can anyone point me to some code snippets or help regarding these areas:

#1: Using Application.GetOpenFilename() on that platform? User gets Error
1004 when trying it the Windows way.

#2: Once I've got a file path/name, how to open it for read? The file is
actually a text file, one field of a record per row, with several rows making
up one record. To be specific, trying to read a QIF file - Intuit Quickbooks
format. Like a .csv file, it's basically just sequential ASCII data.

#3: proper code to do the equivalent of
filebuffer=Freefile()
Do While Not EOF(#filebuffer)
Line Input #filebuffer, OneLineFromFile
.....process that text data
Loop

and of course, How to properly close the text file.

This whole exercise is making me feel quite inadequate!

 

[Bob Greenblatt]

I need help helping someone. They are using Excel on a Mac and apparently I
know even less than I thought I did about programming in VBA on that
platform (which was pretty much nothing - no experience with it at all).

Can anyone point me to some code snippets or help regarding these areas:

#1: Using Application.GetOpenFilename() on that platform? User gets Error
1004 when trying it the Windows way.

I don't know what you mean by the "windows way" this works the same on the
Macintosh. Of course you need to specify colons ":" as the path node
separator instead of "\". Macintosh also does not use "extensions" to
qualify file types, but instead, the Macintosh file type code. Look at the
example in VB help.

#2: Once I've got a file path/name, how to open it for read? The file is
actually a text file, one field of a record per row, with several rows making
up one record. To be specific, trying to read a QIF file - Intuit Quickbooks
format. Like a .csv file, it's basically just sequential ASCII data.

You open it just like any other file. Workbooks.open, filename:=xxxx, or use
the opentext method. Again, check VB help.

#3: proper code to do the equivalent of
filebuffer=Freefile()
Do While Not EOF(#filebuffer)
Line Input #filebuffer, OneLineFromFile
....process that text data

Loop

Line input should work fine.

and of course, How to properly close the text file.

Close should also be the same. Are you having any specific problems? If so
post the code and the errors or incorrect actions you are getting.

 

[JLatham]

Bob,
Thanks for that info. It actually helps some. My problem is that Excel VB
Help is not providing much in this area. When I type the word Macintosh into
the VB Help, I get a message saying that I've asked about a Macintosh
reserved word (not really, but), and that I should refer to the help file
that came with my Excel for Mac software! Naturally I don't have such a
thing here.

In the Help topic for the Application.GetOpenFilename() it says nothing
specific about the Mac except that there is one parameter for buttontext that
is only for use on a Mac. It makes no reference to how to specify the file
filter at all, but does indicate that leaving it unspecified, it defaults to
All Files *.* so I'll try just leaving it out of the command and see how that
works for the other individual.

Since everything pretty much hinges on the returned value from the
..GetOpenFilename call, once I get that working, the rest should work - I hope.

Thanks for taking the time to reply and for your assistance.

 

[JLatham]

Added note to request one line of code and to fully respond.

The only error we were getting at the time was the 1004 at the
Application.GetOpenFilename() line. I am presuming that was caused by my
using the windows file types (as *.txt; *.csv) in the call.

Later, while trying to figure this out, I sent him an Excel file with some
test code in it that he could not get to run. Stated that he got Run Time
Error 438, Object does not support this property or method, but he didn't
indicate which line it was indicating. Presumably that was before the code
even began to run since the first executable line of the code shown below is
setting up an error trap to help find out what went wrong. The idea was for
him to type in a full well formed path and filename to a file on his sytem
and then click a graphic (text box) which has this code assigned as the macro
to run when it is clicked. Works just fine, as did the other code, on my
Windows systems, and without a Mac system to experiment with, I'm just left
scratching my head. And as I noted earlier, the Help in Excel and VBA is not
giving me any real information about changes I need to make to ensure it
works properly on a Mac.

The user is using Excel X with Mac OS X 10.4.8

One question, presuming that I get the .GetOpenFilename() to work and return
the user's choice, would you be so kind as to provide the line of code to
open that file for input as a text type. The file is nothing but
single-record-per row ASCII text, so opening as a .txt or .csv file would be
fine, although it's a .qif file by name. Any change needed to this code,
presuming myFileName has the full path:filename in it and that myFileNum has
a buffer returned by the = FreeFile() function:

Open myFilename For Input as #myFileNum
any change needed?

Sub TestMacFileOpen()
Dim testFilename As String
Dim FileNum As Integer
Dim WhatStage As String
Dim RawData As String

On Error GoTo ItBlewUp
testFilename = Trim(Sheets("Sheet1").Range("A2").Value)
If testFilename = "" Then
MsgBox "Filename is empty. Cannot continue.", vbOKOnly, "Cannot Retrieve
Filename"
Exit Sub
End If
WhatStage = "1 Getting a file buffer number to use."
FileNum = FreeFile()
WhatStage = "2 Attempting to open the file for use."
Open testFilename For Input As #FileNum
WhatStage = "3 Opened OK. Trying to read a record."
Line Input #FileNum, RawData
WhatStage = "4 Opened OK, read 1 record OK, trying to close the file now."
Close #FileNum
MsgBox "All operations succeeded!! We are on our way."
TerminateProcess:
On Error GoTo 0
Exit Sub
ItBlewUp:
MsgBox "An error was encountered. More information follows this message"
MsgBox "The error # is: " & Err.Number & vbCrLf & "Error Message is: " &
vbCrLf & Err.Message & vbCrLf & vbCrLf & "More to follow..."
MsgBox "Error took place at 'stage' " & vbCrLf & WhatStage & vbCrLf & " in
the test process. End of Messages."
Resume TerminateProcess
End Sub