Macro to Open Text File 2

[DM2]

I need a macro that runs every time the spreadsheet is launched, which will automatically run the "Text Import Wizard" on a specific directory.

I have a DOS batch file that executes when you use the "RIGHT MOUSE" button and execute a Short Cut menu option in Windows Explorer I created. When I use this short cut menu on a directory of my choosing, the DOS batch file prints the files in that directory to a text file and saves the text file as "00.TXT" in the "C:\Utilities\Print Directory\"

What I would like to do next is have the batch file automatically open Excel and have excel point to that directory so I can import the text.

I'm not very good with Excel 2003 macro's and I've done some searching on the web for answers but I still haven't figured it out. Below is my current macro:

--------------------------
Sub Import_Text_File()
myFile = Application.GetOpenFilename("Text Files,C:\Utilities\Print Directory\*.txt")
Workbooks.OpenText Filename:=myFile
End Sub
---------------------------

My 1st problem with the macro is that once I've selected the file and clicked "OPEN" the "Text Import Wizard" doesn't run to let me make adjustments.

My 2nd Problem is that I want this macro to execute automatically and that's not happening.

Any Suggestions?

 

[cowski]

How about if you make your batch file do the work?

I've attached a windows script file (files.vbs) that will output the files in a directory straight to excel. To use it drag and drop a folder icon on top of the files.vbs icon. You should be able to adapt it to work like your existing batch file (ie right click on the desired directory).

 

[DM2]

Cowski,
This sounds interesting, but how do I edit it? What Program do I Use.

Also I tried to drag the file folder to the icon and is moved the folder to the same directory as the file?

 

[cowski]

You can edit it with any text editor (such as notepad) as you would a DOS .bat file. As for the folder moving, it sounds like you dragged but didn't drop. You need to drop the folder onto the icon as if you were dropping a file icon onto a folder icon to move it. When done properly the folder won't move but the script file will open Excel and write the file names in the directory.

 

[DM2]

Ok...now I realize the error in my ways...

I was moving the folder in Windows Explorer and dropping it on the script file in Explorer. I moved the script file to my desktop and it worked great.

I may help to explain the purpose of all this.

We have a Document Transmittal with a "DATA" tab. Previously when I imported the text file into Excel, I'd have to adjust the "Fixed Deliminator" to suit the file name. Once I finished, I'd have to COPY and PAST SPECIAL/VALUES into my Document Transmittal, DATA tab. The next time I send drawings to my customer, I'd simply Rev Up the previous Document transmittal.

The best solution would be to do this all in the Document Transmittal and save myself several steps. I'm just not that skilled at Excel Macro's. I have a DOS Batch file that converts the directory to a Text file and then opens up Excel automatically. This doesn't eliminate the "COPY & PAST" step however.

Thanks for your help. Any suggestions?

 

[brengine]

I still don't understande what you are wanting to do?

I need a macro that runs every time the spreadsheet is launched, which will automatically run the "Text Import Wizard" on a specific directory

or

What I would like to do next is have the batch file automatically open Excel and have excel point to that directory so I can import the text.

or

I have a DOS Batch file that converts the directory to a Text file and then opens up Excel automatically. This doesn't eliminate the "COPY & PAST" step however.

and what is the "Text Import Wizard"?

and what is the "Document Transmittal"? An Excel file?

As I understand it now:
1) You have an existing routine that create a text file consisting of a list on filenames in some directory.
2) At the end of the routine, it opens Excel.

...Now what? Is there a certain file in Excel that needs to be opened (and on certain tabs), or is a new file in Excel acceptable? Or run "Text Import Wizard"...what is the output of that and where? Or (as your subject suggests) run an Excel "Macro to Open Text File"?

Then at some point you get a list of something in Excel that you format or Search&Replace or something, then Copy, Paste Special into "Document Transmittal with a "DATA" tab" (is this some other Excel file?).

I just don't have enough information to get a grasp of what you want done.

Maybe rather than trying to explain "how" you are doing this, instead explain *what* you have (as inputs) and what you expect to get (as outputs).

Ken

 

[DM2]

what is the "Text Import Wizard"?

When you open a "TEXT" or "CSV" file in Excel, the "Text Import Wizard" launches.

what is the "Document Transmittal"? An Excel file?

A "Document Transmittal" is a form used a record of what drawings have been sent to a client. In our case we've created a form using Excel.

explain *what* you have (as inputs)

I have a group of drawings that are maintained in a "DRAWING" sub-directory for each individual project. The file name for each drawing starts with numbers followed by a text description of the drawing:
"08-033-9001R03 - Cylinder Mounting Detail.dwg"
The "08-033" is the project number, "9001" is the drawing sheet number, "R03" is the revision number (could be "R0A" if the drawing is preliminary)

what you expect to get (as outputs)

I want to be able to bring the above information into the "DATA" tab of a spreadsheet with each of the above fields in separate columns of the Document Transmittal spreadsheet, starting at row 4.
The Document Transmittal resides in a separate directory:
C:\08-033 - Chevron West Africa
\01 - Design Info
\08-033-T001 - Cylinder detail drawing.xls (native document transmittal file)
\02 - drawings
\08-033-9001R03 - Cylinder Mounting Detail.dwg (native drawing file)
\03 - Transmittal
\08-033-9001R03 - Cylinder Mounting Detail.pdf (we convert the dwg file to a pdf
\08-033-T001 - Cylinder detail drawing.pdf (we convert the spreadsheet to a pdf:

Col B Col C Col D
Drawing No. Rev. No. Title
Row 4 08-033-9001 3 Cylinder detail drawing

Currently we use the DOS batch file to send the "DIR>Print" command to a text file and than import it into the blank spreadsheet. Once every thing is formatted, we "Copy" and "Past \ Special" into the b]08-033-T001 - Cylinder detail drawing.xls[/b] document transmittal.

 

[brengine]

Thanks for the extra info! Couple more questions tho...

Currently we use the DOS batch file to send the "DIR>Print" command to a text file

Is this in a specific location that you are expecting you Excel macro to automatically know?

Or maybe...is this always "C:\Utilities\Print Directory\00.TXT" or at least always in "C:\Utilities\Print Directory\"?

You are manually browsing and opening/importing the text file to:

and than import it into the blank spreadsheet.

...right?

Ken

 

[DM2]

Is this in a specific location that you are expecting you Excel macro to automatically know?

Yes, I'd like the macro to automatically know where the text file is.

Yes, When the DOS batch file executes it automatically saves the file to the C:\Utilities\Print Directory\00.TXT, following is the batch file:
@echo off
REM --------------------------------------------------------------------------------------------------
REM The following Line uses the wild card "%1" to place the desired directory in memory.
REM The "p" is the "Pause" command. Placing a "-" in front of the command line, tells Windows NOT to Pause
REM the output but simply do a text dump. The "/o" in "/o:gn>" tells windows sort the printout. the ":"
REM is a "Switch" seperator, and the "g" tells Windows to print the "Group Directories First".
REM The "n" tells Windows to sort by "Name" and in Alphabetic Order.
REM The ">" redirects the results of the command to store the values in a file which is
REM located in the "C:\Utilities\Print Directory" as the file name "00.txt"
REM --------------------------------------------------------------------------------------------------

dir %1 /-p /o:gn>"C:\Utilities\Print Directory\00.txt"

REM --------------------------------------------------------------------------------------------------
REM This line tells windows to open the file that was just created in the step above using Notpad
REM This allows the user to save the file to a different file name or directory. The Command above
REM will always overright the previous information strored in the "00.txt" file and it may sometimes
REM be usefull to save the file to a different name for later use.
REM --------------------------------------------------------------------------------------------------

Start /w notepad "C:\Utilities\Print Directory\00.txt"

Pause
exit End of Batch File.

If the user decides to save the file as a different file name the information is still stored in the 00.txt text file so I'm thinking that it may not be too important for the user to have an option to select a different file name.

You are manually browsing and opening/importing the text file...

Yes, we're manually browsing which seem silly since the file is always C:\Utilities\Print Directory\00.TXT even if the user saves it to another name, the 00.txt will be overwritten with the latest information.

Thanks for the help!
Dan

 

[cowski]

OK, I finally had some time to look at this again this morning. Attached is a spreadsheet with a macro that will let you browse to the folder of interest then will cycle through all the files in the folder and will split the file names into the drawing number, revision, and title as you described in a previous post. It starts adding the information in the current row, so be careful what cell you have selected before you run the macro.

 

[DM2]

Cowski,
Thanks ever so much for your input. This did the trick. I took a look at your code as well as the website documented in your code.

You've helped me out tremendously.

Regards
Dan