VBA for Excel: Open method

Question

Folks....I have a macro which opens up a .GPX file (contains GPS data using XML tags). When I wrote the macro in Excel 2000 the "WORKBOOK.OPEN filename" statement would open this file into Excel basically as a text file, ie one column with "n" rows. I could then parse it to change/correct data as needed.

However, with Excel 2003 the same macro now opens these files using some kind of an XML filter/schema which totally messes up my macro. Is there a way to force this macro to open the file in the previous "text"-like format? I have tried the OPENTEXT method and it does the same thing as "OPEN".

I'm hoping it is just an option in the OPEN or OPENTEXT method that I have missed???

Thanks....RDK

Answer 1

Well, at least my offering does not require you to change the filename extension from .GPX.

I didn't know if you were parsing each line as it is read from the file or if you were waiting until it was all entered, but assumed not until all was entered since using .OPEN would present you with a full sheet of information.  So you should be able to use the code in place of a .Open statement.

One change you may want to make would be to put this statement somewhere ahead of the Do While loop start:

ThisWorkbook.Worksheets("Sheet1").Cells.ClearContents

change Sheet1 as needed. What it would do is erase any old information on that sheet before refilling it with current contents of the .GPX file.  Note that it will always begin filling at row 2 even on a completely empty sheet.

Answer 2

Thanks.  I was hoping to avoid this type of solution, but if no one comes up with something as simple as the WORKBOOK.OPEN I had working in Excel 2000, I'll give it a shot.

Thanks again....RDK

Answer 3

I think that OpenXML may work for you, but just in case it does not then you could try treating it as a simple ASCII text file and use the following code to read it into your workbook line by line and then parse the rows of data just as you do now after the file has been completely read.  Here's a code snippet that should do the job for you.  You'd need to provide the full path\name to the .GPX file and also change the name of the sheet that the data is being written into.

Dim fileName As String

Dim fileBuffer As Integer

Dim fileData As String

'get the full path\filename into fileName

'needs to be set to full path\file name of your .GPX file

fileName = theFile ' I had a name already set up in theFile

fileBuffer = FreeFile()

Open fileName For Input As #fileBuffer

'loop through each line of the source file

Do While Not EOF(fileBuffer)

  'get one line of text from the file

  Line Input #fileBuffer, fileData

  'put the text into the next available row

  'on Sheet1 in column A

  ThisWorkbook.Worksheets("Sheet1"). _

   Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = fileData

Loop

'close the source data file

Close #fileBuffer

'now you can parse the input data just as before

Answer 4

Thanks, but I do not want to open it as an XLM file.  I tried your suggestion and all of the load options try to load the file according to some XLM format or schema. 

I just want it to load like a simple text file. I could change the extension to txt, but I prefer not to have to do that. Thanks....RDK

Answer 5

See if OpenXML works.

OpenXML Method \[Excel 2003 VBA Language Reference\]