Starting Excel and doing "stuff"...

[Jeff]

WOW... I've finally found a beacon of hope .

Q: Can I use "something" to access a file using EXCEL, then use some
"wizardry" to make changes to the file?

Let me start with, I have been a mainframe programmer for about a hundred
years, but I know next to nothing about Excel or the PC/LAN environment. But,
with some research I have figured out there may be some way to automate a
process that my team does many times a day.

I have created a bunch of mainframe stuff that results in a comma delimited
..txt file being TCPIP'ed to a LAN directory. Once it is on the LAN I open
EXCEL and manually do a series of steps. What I am hoping to accomplish is to
use WMI to watch the LAN folder to see when a new .txt file is TCPIP'ed into
it. Then I want to "magically" access EXCEL and in the background, open the
..txt file, and apply the manual steps (which I'll outline below). I have
already played with WMI a bit and have the watching the LAN directory for new
..txt files working, but I have no clue how to access EXCEL and apply the
manual steps.

Here are the manual steps:

+++ Open EXCEL
+++ File/Open/ (open .txt file to be modified)
+++ Page 1 of Text Import Wizard comes up and I select "Delimited" > Next
+++ Page 2 of Text Import Wizard comes up and I uncheck "Tab" and check
"Comma" > Next
+++ Page 3 of Text Import Wizard comes up and in the Data Preview I select
all columns, and select "Text" as the Column data format > Finish
+++ Excel now shows the data. I do Ctrl+A to Select All
+++ Format/Column/AutoFit > Enter
+++ File/Save As
+++ Change Save As Type to "Microsoft Excel 97 - Excel 2003 & 5.0/95
workbook(*.xls)" > Save

So, that's it. Am I out of my mind, or is there a way to do that?

 

[Joel]

The best way to start is to record a macro and post if so it can be modified.

1) From spreadsheet go to menu - Tools - Macro - Record Macro
2) Run the steps you described below
3) Stop record Tools - Macro - Stop Recording
4) the macro will be in the VBA window under moduls. This code can be
modified


The macro can be placed in a workbook Open envent so it will automatically
run.

 

[Jeff]

COOL... Thanks Joel... I'll go do that now (well, soon as I get through my
morning folder of things to do... but yes... I'll do that today).

Jeff.

 

[Jeff]

Well Joel... nothing is ever easy.

When I try to record the macro I get an error "Too Many line Continuations"
and the macro recording terminates. Below is the macro code I end up with.
But it seems important that when I open the .txt file the "Text Import
Wizard" comes up... I don't think I see anything in the macro code from when
I completed the "Text Import Wizard" pages.

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\TRANSFER\SYOPMAINFRAME\Conversions\Kernmore.jjk\TEST.TXT",
Origin := _
437, StartRow := 1, 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,2),Array(5,2),Array(6,2),Array(7,2),Array(8,2),Array(9,2),
_

Array(10,2),Array(11,2),Array(12,2),Array(13,2),Array(14,2),Array(15,2),Array( _

16,2),Array(17,2),Array(18,2),Array(19,2),Array(20,2),Array(21,2),Array(22,2), _

Array(23,2),Array(24,2),Array(25,2),Array(26,2),Array(27,2),Array(28,2),Array( _

29,2),Array(30,2),Array(31,2),Array(32,2),Array(33,2),Array(34,2),Array(35,2), _

Array(36,2),Array(37,2),Array(38,2),Array(39,2),Array(40,2),Array(41,2),Array( _

42,2),Array(43,2),Array(44,2),Array(45,2),Array(46,2),Array(47,2),Array(48,2), _

Array(49,2),Array(50,2),Array(51,2),Array(52,2),Array(53,2),Array(54,2),Array( _

55,2),Array(56,2),Array(57,2),Array(58,2),Array(59,2),Array(60,2),Array(61,2), _

Array(62,2),Array(63,2),Array(64,2),Array(65,2),Array(66,2),Array(67,2),Array( _

68,2),Array(69,2),Array(70,2),Array(71,2),Array(72,2),Array(73,2),Array(74,2), _

Array(75,2),Array(76,2),Array(77,2),Array(78,2),Array(79,2),Array(80,2),Array( _

81,2),Array(82,2),Array(83,2),Array(84,2),Array(85,2),Array(86,2),Array(87,2), _

Array(88,2),Array(89,2),Array(90,2),Array(91,2),Array(92,2),Array(93,2),Array( _

94,2),Array(95,2),Array(96,2),Array(97,2),Array(98,2),Array(99,2),Array(100,2), _

Array(101,2),Array(102,2),Array(103,2),Array(104,2),Array(105,2),Array(106,2), _

Array(107,2),Array(108,2),Array(109,2),Array(110,2),Array(111,2),Array(112,2), _

Array(113,2),Array(114,2),Array(115,2),Array(116,2),Array(117,2),Array(118,2), _

Array(119,2),Array(120,2),Array(121,2),Array(122,2),Array(123,2),Array(124,2), _
End Sub

 

[Joel]

See if this code works better

Sub Getfixedtext2()

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const MyPath = "S:\TRANSFER\SYOPMAINFRAME\Conversions\Kernmore.jjk\"
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0


Set fsread = CreateObject("Scripting.FileSystemObject")


ReadFileName = "text.txt"


'open files
ReadPathName = MyPath & ReadFileName
Set fread = fsread.GetFile(ReadPathName)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

RowCount = 1
Do While tsread.atendofstream = False
InputLine = tsread.ReadLine
For ColCount = 1 To 124
Cells(RowCount, ColCount) = Mid(InputLine, 2 * (ColCount - 1), 2)
Next ColCount
RowCount = RowCount + 1
Loop
tsread.Close
End Sub

 

[Joel]

By the way, you don't need the field delimiter when you are using commar
delimited. The field info is if you are reading fixed space. I think you
can simply remove the fieldinfor from you original code and it should work.

 

[Jeff]

OK Joel… this is great and THANK YOU.

I am really a fish out of water here… but here is what I am able to put
together. I have the code from the first macro that was above the field info
(this is the part that converts the .txt file to a comma delimited text
format file?), and then I have added the macro code for the remainder of the
manual things I do (Select and autofit all columns, then SaveAs). Is this
what you were suggesting I do?

@@@

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\xxx\Kernmore.jjk\TEST.TXT",
Origin := _
437, StartRow := 1, DataType := xlDelimited, TextQualifier :=
xlDoubleQuote, _
ConsecutiveDelimiter := FALSE, Tab := FALSE, Semicolon := FALSE,
Comma := TRUE _
, Space := FALSE, Other := FALSE
Range("A1:FB829").Select
Selection.Columns.AutoFit
ChDir "S:\xxx\Kernmore.jjk"
ActiveWorkbook.SaveAs Filename:= _
"S:\xxx\Kenmore.jjk\TESTTEST.xls", FileFormat _
:=xlExcel9795, Password:="", WriteResPassword:="",
ReadOnlyRecommended:= _
False, CreateBackup:=False
End Sub

@@@

Is what I have close to right?

This will be so very very cool if I can get it working.

Thanks, Jeff

 

[Joel]

Recorded macro create a lot of unnecessary code. Wha tyou havve should work.
Here is a cleaned up version

ub AutoIT()
'
' AutoIT Macro
' Macro recorded 1/25/2008 by jjken
'

'
Workbooks.OpenText Filename := _
"S:\xxx\Kernmore.jjk\TEST.TXT",
Origin := 437, StartRow := 1,_
DataType := xlDelimited, _
TextQualifier := xlDoubleQuote, _
Comma := TRUE _

Range("A1:FB829").Columns.AutoFit
ChDir "S:\xxx\Kernmore.jjk"

ActiveWorkbook.SaveAs Filename:= _
"S:\xxx\Kenmore.jjk\TESTTEST.xls", _
FileFormat:=xlExcel9795
End Sub

 

[Jeff]

Holy COW! Unbelievable!!! That’s it? WHOA! (can you tell I’m impressed and
excited : -).

I suppose the next thing to do is to try and run this from some command line
somewhere (or something???)?

Oh, I do see one thing I wonder about? It looks like Range has a hard coded
“Last Cellâ€. The .txt files I am dealing with are of varying size… does this
matter?

I can’t believe how helpful you are being. I was on the verge of thinking
this was something beyond what I could figure out. This is the coolest stuff
EVER!

Thanks and THANKS :- )))
Jeff

 

[urkec]

I suppose the next thing to do is to try and run this from some command line
somewhere (or something???)?

You need to set up a WMI permanent event subscription. First, you create an
instance of the __EventFilter Class in root\cimv2 that contains the WQL
notification query that filters the events you are interested in. You can use
CIM_DataFile and __InstanceCreationEvent classes. Second, you need an event
consumer. You can use one of the standard event consumer classes, maybe
ActiveScriptEventConsumer or CommandLineEventConsumer (you can run Excel
either using command line or from a VBScript file - I'm not sure about
CommandLineEventConsumer, but you need to MofComp the
ActiveScriptEventConsumer before you can use it). Third, you need to
associate the __EventFilter and the consumer class you chose with
__FilterToConsumerBinding Class.
Maybe it would be better for you to use the ActiveScriptEventConsumer
because you can access Excel.Application object from VBScript and execute any
code you need from there.

 

[Jeff]

Holy WHAT? Oh Urkec :-/. I am a mainframe programmer, in fact a smart one,
but I could make more sense of Portuguese than I could of that. I believe you
when you say that’s what I need to do. BUT, if that IS what I have to do I
think my ship is sunk :-/.

I have a WMI .vbs that monitors a folder for new files. I was thinking it
would be something easy, like putting the commands Joel gave me in the .vbs
along with the WMI code.

This is asking a lot. But this will be so cool and helpful to my team here,
that I really would like to make it work. I think if you give some code
examples (or code I could really plug in), and say some more things about
this I’ll do what I can to make it work.

Thank you,
Jeff

 

[urkec]

Holy WHAT? Oh Urkec :-/. I am a mainframe programmer, in fact a smart one,
but I could make more sense of Portuguese than I could of that. I believe you
when you say that’s what I need to do. BUT, if that IS what I have to do I
think my ship is sunk :-/.

I have a WMI .vbs that monitors a folder for new files. I was thinking it
would be something easy, like putting the commands Joel gave me in the .vbs
along with the WMI code.

This is asking a lot. But this will be so cool and helpful to my team here,
that I really would like to make it work. I think if you give some code
examples (or code I could really plug in), and say some more things about
this I’ll do what I can to make it work.

The first thing to do is to compile scrcons.mof from the command line with
the mofcomp utility:

mofcomp -N:root\cimv2 c:\windows\system32\wbem\scrcons.mof

This will register the ActiveScriptConsumer class with WMI. Depending on
your Windows version you may need to change the path to the scrcons.mof.
Mofcomp.exe is located in c:\Windows\system32\wbem\ , but it is in my %PATH%
so I don't need to specify the full path.

Next, create instance of the WMI classes I mentioned. This is test VBScript
code, so you may need to change it to suit your requirements:


'connect to WMI locally
'can't use WScript object in Consumer.vbs
'MsgBox will not error out but will not display

strComputer = "."
Set objSWbemServices = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" _
& strComputer & "\root\cimv2")

'create an instance of __EventFilter class

Set objEventFilterClass = objSWbemServices.Get("__EventFilter")
Set objEventFilter = objEventFilterClass.SpawnInstance_()

objEventFilter.Name = "TestFilter"
objEventFilter.QueryLanguage = "WQL"
objEventFilter.Query = _
"Select * From __InstanceCreationEvent " &_
"Within 5 Where TargetInstance " &_
"Isa 'CIM_DataFile' " & _
"And TargetInstance.Name = 'C:\\test.csv'"

objEventFilter.Put_

'create an instance of ActiveScripteventConsumer class

Set objConsumerClass = objSWbemServices.Get _
("ActiveScriptEventConsumer")
Set objConsumer = objConsumerClass.SpawnInstance_()

objConsumer.Name = "TestConsumer"
objConsumer.ScriptFileName = "C:\Consumer.vbs"
'it is .ScriptingEngine instead of .ScriptEngine
objConsumer.ScriptingEngine = "VBScript"

objConsumer.Put_

'need to refresh objEventFilter and objConsumer
'before I can use their Path_ property

objEventFilter.Refresh_
objConsumer.Refresh_

'create an instance of __FilterToConsumerBinding class

Set objBindingClass = objSWbemServices.Get("__FilterToConsumerBinding")
Set objBindingInstance = objBindingClass.SpawnInstance_()

'Filter and Consumer properties are strings containing the absolute path
'to __EventFilter and ActiveScriptEventConsumer instances just created.
' If I want to hardcode the paths I need to use double quotes for Name
property
'doesn't work without '\\.\' part

objBindingInstance.Filter = objEventFilter.Path_
objBindingInstance.Consumer = objConsumer.Path_

objBindingInstance.Put_()


This code first creates an event filter that polls the repository every 5
seconds for a file creation event (the file name here is c:\test.csv, but you
can change it). Then it creates an active script event consumer instance and
sets it's ScriptFileName property to c:\consumer.vbs. This is the VBScript
file that will be executed each time a new file (named c:\test.csv) is
created. Then it uses __FilterToConsumerBinding to associate the event filter
and the consumer.

You only need to run this code once, and the subscription is stored in the
repository permanently. While testing or when you want to cancel the
subscription you need to delete all three instances from the repository (you
can use another script or utilities like wbemtest.exe or CIM Studio).

Consumer.vbs can contain the code for the .csv file processing. This is the
test code I used, it just saves a .csv file as an Excel workbook, but you can
change this to your needs:


Set objExcel = CreateObject _
("Excel.Application")
objExcel.DisplayAlerts = False

Set objBook = objExcel.Workbooks.Open ("C:\test.csv")

objBook.SaveAs "C:\Test.xls"
objExcel.Quit

Active script event consumer has limitations, you can't make Excel visible,
no MsgBoxes etc. but I think you can do what you need with it.

The VBS script you have is probably a temporary event consumer. It monitors
a folder for file creation while it is running, but I think permanent event
consumers are more reliable, you have the subscription stored in the WMI
repository so you don't need to have a script running all the time. WMI
receives an event from the file system and only then runs a script.

Hope this helps some.

 

[Jeff]

urkec:

You are so kind to put so much time and effort into this... THANK YOU. I
have meetings and unavoidable work today so I'll not be able to get to this
til tomorrow. But, looking at what you wrote I think I will be able to give
it a good try.

Again, thank you both Urkec and Joel.
I'll be back with an update (or questions :-/) after I've seen what I can do
with this.
Jeff

 

[Jeff]

Joel and Urkec. I'm wondering if I would be better off getting the part that
starts .xls and does 'the stuff' working first, and then worry about how to
'watch' and invoke it (via WMI "folder/file monitoring", or with a WMI
permanent event subscription). What I am thinking makes sense is to try and
get Joel's code working first, and then to tackle setting up the WMI perm
event subscritpion.

So, as the next step........ can I execute Joels code from a command line or
simple .vbs 'wrapper'... or???

Thanks Joel and Urkec (and anyone else following this thread).
Jeff.