Replies

Not just that, but if I specify Fixed Width when using texttocolumns, Excel assumes that regardless of the width of columns that I set, I really want it to remove leading blanks!

Sheesh, it just shouldn’t be this difficult!

Can’t Microsofty understand that sometimes people would like to decide for themselves what they want to do!

Anyway that works.

I’d still like to know why the paste sometimes does one thing and sometimes does another.

But anyway, thank you once more Hans for all your assistance.

I just tried changing the deler to | and executiong this command

Workbooks.OpenText Filename:=varFile, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=”|”, FieldInfo _
:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1), Array(5, 1), Array(6, 2)), _
TrailingMinusNumbers:=True

and it totally ignores the parsing information!

It OpenText just broken?

Here is some of the input data:

01|  |M|1996-08-01|2099-12-31|0123456789
01|  |1|1996-08-01|2099-12-31|0123456789
01|  |3|1996-08-01|2099-12-31|0123456789
02|  |M|1996-08-01|1999-06-03|01234569
02|  |1|1996-08-01|1999-06-03|01234569
02|  |3|1996-08-01|1999-06-03|01234569
03|  |M|1996-08-01|2004-08-20|01234569
03|  |1|1996-08-01|2004-08-20|01234569
03|  |3|1996-08-01|2004-08-20|01234569
03|01|M|2004-08-10|2004-08-19|01234569
03|01|M|2004-08-27|2099-12-31|0123456789

But surely adding the parameters on the OpenText call itself should cause the correct parsing???

I even tried altering the OpenText parameters to parse the file at open time thus:

        Workbooks.OpenText Filename:=varFile, 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, 1), Array(5, 1), Array(6, 2)), _
        TrailingMinusNumbers:=True

But it still loaded the file with the columns as General and stipped off the leading zeros.

I tried that, but it fails as when it opens the .CSV file, it has already parsed the data into separate columns. So .Range(“A1”).CurrentRegion. is more than one column wide and it fails because it texttocolumns can only be used on a single column range.

Hi Hans,
I still don’t quite get it I’m afraid.
I have a lot of these .CSV files. Currently I just open them from whatever folder I happen to be in in Windows Explorer (Sent To->Notepad). This is easy. Then I copy and paste into Excel.

Sometimes Excel pastes all into column A (this is ideal), sometimes it parses into columns and presents mw with the “floating icon” to select the Text import wizard (not so good).
I can find no common denominator for why it sometimes chooses one or the other.

Now, how would I go to a folder and get Excel to open the file in a new window using the Workbooks.OpenText method that you suggest?

Hi Hans, I don’tunderstand what you mean by “open the text file in a new window, then copy the contents into your workbook.”

How does this differ from opening the text file and pasting it in (which was what I said that I did)?

And also on the “OpenText” method, I would rather paste the data in as it tends to be quicker to openthe csv file in Notepad and paste into the correct area on my sheet.

Contacting the ISP is not possible.

Thanks for your reply. I guess I ought to expand a bit on what PD I
have already done.

First let me explain how I found the problem in the first place:

I work from home, connected to work via a VPN.

My work machine (ThinkPad) and another PC (Netvista) are both connected to
an ADSL Router Modem.

I was getting to these sites with no problem at all from my ThinkPad.
However, when I went to the Netvista, Internet Explorer said that it
couldn’t find the site.

So thought I, what is the difference between these 2 machines. The
difference that I came up with was that the ThinkPad had a virtual network
adapter (the VPN) with work’s DNSs, whilst the Netvista was relying on only the
DNS known by the router modem. (By the way, I’d love to know how windows
decides whether to send stuff down the real network adapter or the virtual
one).

Hence I tried the NSLOOKUP approach. NSLOOKUP with no server parameter heads
straight off down the real network adapter and could not resolve the
address. However, NSLOOKUP when supplied with the work DNS (that is
registered with the virtual adapter) resolved the address perfectly.

So I asked my wife (who is connected at her office on an ADSL connection
with a different ISP) what name servers she was using. Once again NSLOOKUP
when supplied with one of these DNS resolves the address.

True,
I guess I realised that, but that doesn’t really cause the formatting to “stick”, rather it automates correcting it each time.

I was expecting that there would be a way to tell excel that this is the formatting I want regardless of the data that it is charting.

Hi again,
The attached sheet has a pivot chart with data labels formatted as Position Center and Orientation 90 degrees.

However, every time I refresh the data the Labels lose this formatting.

How do I make this formatting stick?

Also, the pivot table is sorted on the Count column. when I add new data and refresh the table, the sort order is lost.

Can I make this stick too?

TIA

Oops, I completely forgot to post the attachment!!!

Thanks Hans, I can’t blame wopr here, I completely forgot to attach the file!

Now attached to the original post.

Ahh, I did wonder if that was what it was.

The first drawback that I have found to dynamic ranges

Ahh, when you said online help, I thought you were referring to the web!

Ahh, when you said online help, I thought you were referring to the web!