excel.workbooks.opentext compile error (Excel 2003) 2

[AndyKeen]

Hi

I have a spreadsheet which open a csv file and converts it to another csv format (processing the rows as it goes). All worked if I opened the input csv file with the statement:

Set wbIN = Workbooks.Open(Filename:="g:Trans.csv")

except that the second column is sometimes misinterpreted as a date e.g. account No "MAR1" is converted to a datevalue before I can get to it.

To get around this I have tried to open the input csv as follows:

Set wbIN = Workbooks.OpenText(Filename:="g:\Trans.csv", DataType:=xlDelimited, textQualifier:=xlTextQualifierDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True, FieldInfo:=Array(Array(2, 2)))

but no matter what syntax I try I now get the compile error:

Expected Function or variable

with the .OpenText highlighted

What am I doing wrong? Please help, I am tearing my hair out here (and I haven't got much left to lose!). I have checked the MS help, the web etc etc but can't see what I have got wrong. I am sure it is obvious but.....

Many thanks

 

[SkipVought]

Hi,

Do not OPEN the csv.

Rather IMPORT, using Data > Get External Data > IMPORT. In the IMPORT, you can specify that the column is TEXT.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[AndyKeen]

Thanks for the reply Skip but I think on this occasion it misses the point. I am trying to use a small spreadsheet to do a repetitive conversion task. The worksheet just has a button on it which the user presses to activate the conversion code behind. The code is(was) all working perfectly execpt for the occasional erroneous conversion of the account number to a date. I have changed it to use, what I believe is a perfectly normal and logical method of opening the csv as a text file and creating a workbook from which I can process. I am getting what appears to be a totally illogical and incorrect error message when I try to compile so my question is why? What am I doing that is wrong and would cause the error to be displayed?

 

[SkipVought]

Sorry that I missread your post.

I too, am puzzled why the Set statement will not work.

I can OPEN without the Set, not with the Set???

    Dim wbIN As Workbook
    Workbooks.OpenText _
        Filename:="D:\My Documents\csv.csv", _
        DataType:=xlDelimited, _
        textQualifier:=xlTextQualifierDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Comma:=True, _
        FieldInfo:=Array(Array(2, 2))
    Set wbIN = ActiveWorkbook

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[combo]

If you look at the object browser:

Function Open(....) As Workbook
Sub OpenText(..)

OpenText returns nothing, that's why the 'Set' fails.

combo

 

[AndyKeen]

Thanks again Skip. I thought I had already done exactly that and got the same error as with the set but I must have made a different error there. Your code worked perfectly!

Unfortunately, my original problem still remains. A text field "MAR2" in the second column of the CSV file, when opened using the opentext appears as Mar-2 in the worksheet even though textinfo for column 2 indicates it should be treated as a text column on reading. If I open the csv file with notepad the field shows as MAR2. Looking at the worksheet immediately after the textopen statement it is Mar-2 and if I manually format the column as text the cell shows a date value!

Sorry to be a pain but do you have any suggestions other than writing a routine to convert it back?

Why is it always the simple things that waste the time?

 

[SkipVought]

And my initial response still stands.

I, too, get the anamoly using OpenText. However, if I use IMPORT, I get TEXT!!!

    With Sheets("Import").QueryTables(1)
        .Connection = "TEXT;D:\My Documents\csv.csv"
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 2)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[combo]

It is enough to change the file name extension: 'csv' => 'txt', and the Opentext starts to work properly.

combo

 

[AndyKeen]

Thanks again to Skip and Combo for your assistance. I will try the solutions tomorrow. Unfortunately I don't control the naming of the import file so Combo's solution might not be ideal for the user. However I am sure one solution or the other will work for me and at least I have learnt something useful for my frustration.

It would make life a lot easier if Microsoft made their software live up to their documentation though!

I don't know what the etiquette is here but gold stars to you both and I hope the thread will be useful to somebody else in the future!

 

[AndyKeen]

Just to complete the story. The final syntax I got working was:

Set wsInqt = wsIN.QueryTables.Add(Connection:="TEXT;g:\vas\sage\trans.csv", Destination:=wsIN.Range("a1"))
With wsInqt
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 2)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
.Refresh
End With

Set wsTrans = wbThis.Sheets.Add(after:=wbThis.Sheets("Import"))
wsTrans.Name = "Transactions"
wsTrans.Columns(1).NumberFormat = "@"

Note that as I was outputting the text value "MAR2" to another worksheet I also had to format the column in the receiving sheet as text otherwise Excel converted my carefully imported text to a date as it moved it! Obvious but it took me a test macro to find the appropriate formatting character.

Thanks again for your help guys and hope this helps somebody else one day.

 

[PHV]

.Refresh BackgroundQuery:=False
.Refresh
Why refreshing 2 times ?

 

[HughLerwill]

- Remove your '.Refresh'
- Replace it with '.Delete', that will break the link with the csv file but leave the imported data in the sheet. I assume you do not want a refreshing link after getting the data into a sheet.
- You can handle formatting in code when you import to the sheet as previously discussed using '.TextFileColumnDataTypes' OR preformat the sheet (use a template) you are importing into; in the latter case the imported data will follow the formats set in the templated sheet.

 

[AndyKeen]

Thanks to HughLerwill & PHV. Will remove the 2nd refresh and try the delete as suggested. As it happens the CSV files are relatively small and the sheet is only active for a very short time (usually less than a minute) during the conversion process so neither is really an issue in real life but it is good to get the benefit of your advice to improve my code.

Re the formatting, with hindsight I should have just preformatted a sheet and used that rather than creating one on the fly each time. The workbook is basically just a template anyway so it doesn't matter how many sheets it contains when it opens up. Will change that too.

Thanks again guys

 

[combo]

Unfortunately I don't control the naming of the import file

You do, you pick the file for import, so you can either rename it (VBA Name command) or work with a copy (Filecopy).

combo

 

[AndyKeen]

Thanks Combo. You are of course correct I can "control" the name if I wish. However, as a test after your first post, I changed the name of the file manually to .txt and I am afraid got the same "automatic conversion" problem as I got with the .csv extension so in this case there isn't much point.

Thanks anyway

 

[combo]

Maybe another ms improvement, in excel xp the 'Opentext' method for '*.txt' files works.

combo