Test if it is Excel file to choose Workbooks.Open or Workbooks.OpenText

[Gavona]

I want to open a file but it could be either a real excel workbook or a tab-delimited text file with .xls extension. If it is a text file then I want to use the OpenText method. So how can I test?

Another approach to the issue would be to change the default parameters that Excel uses when opening a text file. Specifically I want to change the TextQualifier to xlNone

Gavin

 

[SkipVought]

hi,

How are you opening? From Window Explorer? From a command line? From a link? From inside a workbook? Within a VBA program?

Please state the functional process?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

BTW, I very rarely use the OpenText method. I would rather control the parsing of the text than allow Excel to GUESS!

Why would you not know that you are opening an Excel workbook rather than a text file?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Yea, I know that if you're using the OpenText method (VBA) so the real questions revolve around process and intent.

With OpenText, you also need to know the field data type assignment. You can ignote that, but if you do, Excel will GUESS at the data type.

For instance, I have text files that have what appear to be NUMBER columns, and if I ignore, I get NUMBERS, but I really need TEXT. In somes instances, Excel actually CHANGES the data to a value entirely different that what is intended. For instance, I have a TEXT column that can contain values such as this (these are machine identifiers)
[tt]
145BN
16523
171E2
[/tt]
Ignoring data type I get

[tt]
145BN
16523.0
17100.0
[/tt]
(decimal points added for empahasis) and notice that 171E2 is CONVERTED to 17100!!!

So philosophically, how could you ignore the type of file and the types of data? I face this every day, so I do the text parsing/data-type thing for everything I might have to import.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Thanks Skip, some more background for you: SAP generates files that it describes as exporting to Excel but in reality it seems to be text delimited but with a file extension of XLS. When excel is used to open the file it guesses at how to import it, as you say. The default guesses are ok except for an issue which totally messes things up if there are quotation marks in one of the fields. Manually to overcome this one opens from Excel, this triggers the text import functionality and one can manually change (at step 2) the TextDelimiter to (None) which resolves the issue.

Now I have a VBA process which uses the file. So I have written it to use the OpenText method and set the parameters for that as I need them. This works fine if we are (as we should be) using the raw download file. However people have a habit of opening the file, checking that it contains the expected data and then saving it as an excel workbook. I want to know how I can detect that this has been done and take appropriate action.

So I have a file called myFile.xls I suppose I could simply error trap around the Workbooks.OpenText method......

Gavin

 

[SkipVought]

I have similar issues with SAP and Business Object downloads.

Fortunately, I am the one who performs the exports, and I NEVER EVER export a spreadsheet! I ALWAYS export the data as a text file (.csv)

So then, my user application is a Workbook that IMPORTS (Data > Get External Data > Text Files) exactly the way the data needs to be imported and the source data can never be compromised!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

If I performed the process then it would indeed be so much simpler / more reliable! We get commas within our data so csv wouldn't work. Trouble is some of the fields are free text entry into SAP with no validation possible at entry (so we are told). I will look at the other SAP export options.

Gavin

 

[DrSimon]

Not sure if these comments will help in this situation, but thought of a couple of points. I haven't dealt with such problem recently but remember them from old.
When I had some control over the csv export, I would sometimes seed the first row with values that would fool Excel into data typing correctly. e.g. I'd head a column of digits that I wanted typed as text with 'A'; once imported I'd delete the top row. The other point is that commas themselves don't stop the use of csv, because you can use text qualifiers e.g. "1,2,,3","4,XQ