Text File Wizard/Varying Column Widths 1

[richand1]

Hello all.

I have been scouring the internet (including this site) for help with this but have been unsuccesful thus far.

I have 2 text files that I need opened in Excel using fixed width (they are not delimited), but, in all the separate pieces of code that I have found, you need to declare when each column starts. My problem is that the columns will almost always be of varying widths, e.g. the Part Number column will be a different length one month to the next, therefore 'throwing' out the array and all subsequent ones.

So my question is, is there any way to call the text file import wizard so the user can manually set the column widths and then, once Finished is pressed, have the code continue on it's merry way? I have tried recording a macro and going through the motions to see if there is a clue in the recorded code as to how to do this, but I cannot see one.

Many thanks all - this sounds so easy but I cannot find the solution.

Richard

 

[PHV]

Application.Dialogs(xlDialogImportTextFile).Show

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[richand1]

Crikey. I thought it was going to be fairly simple but...!

PHV, you've helped me once again - thank you very much. Is this in VBA help?? I regret wasting people's time with simple queries like this...

Have a star!

Richard

 

[richand1]

PHV, is there any way I can remove the openfile dialogue box from this?

I want to be able to set the filename as a static value so there can be no user-errors when opening the file and then have the code, just like the manual approach and after the column widths have been set, dump the data into a new Excel file while setting the filename as the original text files' - this is so I can reference the name using 'Windows.("RoI.txt").Activate' in order to manipulate it further.

Is there any way to do this?

 

[SkipVought]

Hi,

On your sheet Data > Import External Data > IMPORT...

Once you ADD this querytable to your sheet, all you need do is Data > Refresh with NO PROMPT. You probely need no VBA at all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[richand1]

Skip, thanks and good thinking but, for some reason, it asks for a file location when you refresh, which is exactly what I didn't want: I don't trust the users to be able to navigate to the correct file every time!

Thanks again though.

Rich

 

[richand1]

So, I guess what I need here (just for clarification) is for the text file to open and THEN display the wizard, and THEN dump the data into a file with the same name as the text file (just as it does when you manually open a text file from the Excel environment).

 

[SkipVought]

As I stated previously, it is possible to REFRESH the import WITHOUT a prompt.

Data > Import External Data > Data Range Properties, UNCHECK, Prompt for filename on refresh

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[richand1]

I wasn't sure how to turn the 'file prompt on refresh' off but now I know: Data Range Properties.

This works beautifully initially because the column widths are set with the wizard, but then I added some data at the end of a manufacturer name to see if the refresh could handle it and, alas, it can't: the data in the next cells was pushed right due to the increase in length.

This mimics what will happen regularly with this data so I think I will have to stick to the text-to-columns method.

Thanks again though Skip - I appreciate your time as always.

 

[SkipVought]

" I added some data at the end of a manufacturer name"

Could you explain what that means and post an example. IMPORT uses the same parsing as Text to columns, so I'm having a problem understanding what the difference is.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[richand1]

I wasn't sure how to turn the 'file prompt on refresh' off but now I know: Data Range Properties.

This works beautifully initially because the column widths are set with the wizard, but then I added some data at the end of a manufacturer name to see if the refresh could handle it and, alas, it can't: the data was pushed right due to the increase in length.

This mimics what will happen regularly with this data so I think I will have to stick to the text-to-columns method.

Thanks again though Skip - I appreciate your time as always.

 

[SkipVought]

what? I hear an echo!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

in fact, you actually have GREATER functionality with IMPORT over Text to columns for propogating formulas adjacent and to the right of the querytable.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[richand1]

Strange. I receieved the notification that you'd posted a response, opened up the site from a new IE Window and it hadn't updated...

Anyway, In column B I have a manufacturer of "Henry Ford & Son Ltd". I added " & ltd & ltd & ltd" to the end of the name within the source text file and hit refresh. This new data then pushed the data in the cells to the right of this cell, right along the table.

You see, the column widths that I set in the wizard will need to be changed EVERY time I refresh this data because the data will not always be the same length within the cells.
So, by hard-coding in text-to-columns after Workbooks.Open "TextFile.txt", this will prompt the user to set the column widths.

I can't see any other way around the varying size of the columns.

I hope this makes sense...

 

[SkipVought]

I cannot "see" because you have not posted a cogent example demonstrating what you are stating.

The only thing that I see, is that you don't want a prompt for the FILE, but you do want a prompt for the PARSING because something changes from time to time. Maybe THATS the issue.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[richand1]

My apologies - I didn't know that you could upload files. I still don't know how to, but the Text-to-columns method seems to work so I'm going to stick to that.

Sorry to have wasted your time.