VBScript Needed to Manipulate Excel Sheet

[MikeRI10]

VBScript masters, I need your help. I am not very good at scripting, and what I need is probably very easy for someone here to figure out. Here is what I need the script to do:

1: Open Excel and import a TXT file using delimited text import. Here is the "tuff" part. Certain columns need to be formatted as "text only" BEFORE the data is imported. Otherwise leading 0's will be truncated. If it is easier to write the script to make all cells "text" only prior to the import, that's perfectly fine too.

2: Once the data is imported to Excel, the script needs to scan the "A" column starting at A2. If there is data in a cell (anything but blank/empty), then I need the current date to populate in the respective cell in column "O" (ex: A2 has "12345", then cell O2 needs todays date (5/2/2012) populated. Once a blank cell in column "A" is found, stop and save the file as TXT. The first "blank" cell in column "A" will be the EOF.

I hope this makes sense, and I want to thank everyone in advance for tackling this.

Any questions or if anyone needs clarification, please ask.

Thank You!

 

[SkipVought]

hi,

Is there a reason that you are coding this in VBS, EXTERNAL to the Excel Workbook, rather than using VBA, INTERNAL to the Excel workbook? The IMPORT can do EXACTLY what you want.

Certain columns need to be formatted as "text only" BEFORE the data is imported.

This can be done in the IMPORT process.

Skip,

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

 

[PHV]

Anyway, what have you tried so far and where in your code are you stuck ?

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

 

[MikeRI10]

SkipVought,

Our client uses a BAT (yes, BAT file) file which performs an Export from a DCT (Data Connectivity Tool). It is a simple unattended export which creates the TXT file that needs to be manipulated. If possible, I would simply like to add to the BAT file "scripting" that will kick off this manipulation. Basically, I would like to have one file that the client can launch to perform all steps needed and automate this process. Export to a TXT, open in Excel and add date to column, then import back into the DCT solution.

The BAT file currently does the Export/Import. I just need to "insert" additional language/scripting that would perform this manipulation seemlessly.

I am open to any and all suggestions to make this easy.

Thank you again!