I need help

[Guest]

I have a question on how to import a file that is comma separated into excel
automatically and print it automatically. I build building automation
systems and the system can record temperatures and such, but it saves it in
a comma separated format. I need to take that information and clean it up
so it can be printed at a specific time or day automatically by the computer
itself. I was given a file that will print the selected file when it is
clicked on or ran by the windows scheduler but it does not clean the file up
before it prints it. Here is a copy of that file.
_________________________________________________
printexcel.vbs

Set objExcel = WScript.CreateObject("Excel.Application")
strFileName = "C:\Program Files\Kmc\Wcxl\Flagler College\Data\S6A14TL1.his"
Set objWB = objExcel.WorkBooks.Open(strFileName)
objExcel.ActiveSheet.PrintOut
__________________________________________________

The information below is what the information looks like raw. This is what
I need to clean so that it can be printed like a report.
____________________________________
The file name is S6A14TL1.his

"","",Hwst,Hwsr,Boilerop,""
1,7/25/03 1:18:39 PM,-44.75,-44.8,0.00,0
2,7/25/03 1:18:49 PM,-44.75,-44.8,0.00,0
3,7/25/03 1:18:59 PM,-44.75,-44.8,0.00,0
4,7/25/03 1:19:09 PM,-44.75,-44.8,0.00,0
5,7/25/03 1:19:19 PM,-44.75,-44.8,0.00,0
6,7/25/03 1:19:29 PM,-44.75,-44.8,0.00,0
7,7/25/03 1:19:39 PM,-44.75,-44.8,0.00,0
8,7/25/03 1:19:49 PM,-44.75,-44.8,0.00,0
9,7/25/03 1:19:59 PM,-44.75,-44.8,0.00,0
10,7/25/03 1:20:09 PM,-44.75,-44.8,0.00,0
11,7/25/03 1:20:19 PM,-44.75,-44.8,0.00,0
___________________________________


I would be really appreciative if someone could head me in the right
direction.

Thanks,

Joey

 

[jaf]

Hi Joey,
Rename the file with your code and Excel will drop the commas automatically.

strFileName = "C:\Program Files\Kmc\Wcxl\Flagler College\Data\S6A14TL1.his"
Name strFileName As "C:\Program Files\Kmc\Wcxl\Flagler
College\Data\S6A14TL1.csv"
strFileName = "C:\Program Files\Kmc\Wcxl\Flagler College\Data\S6A14TL1.csv"

 

[joey]

When you said to rename the file with my code , did you mean the
printexcel.vbs file will do it for me. I typed the lines that you gave me
into that file and it gives me a error on the line with --- Name strFileName
As "C:\program files ........ . I need everything to be automatic.
I am not good with code so please excuse me if I'm a little slow to pick
up. I am trying to learn a little.

Joey

 

[Tom Ogilvy]

Sorry - didn't focus on the fact that the .his extension would not treat the
file as CSV. this should do what you want:

Set objExcel = WScript.CreateObject("Excel.Application")
strFileName = "C:\Program Files\Kmc\Wcxl\Flagler College\Data\S6A14TL1.his"
Set objWB = objExcel.WorkBooks.Open(strFileName)
objExcel.ActiveSheet.Columns("A:A").TextToColumns _
Destination:=Activesheet.Range("A1"), DataType:=1, _
TextQualifier:=1, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), _
Array(4, 1), Array(5, 1), Array(6, 1))
obExcel.ActiveSheet.Cells.Autofit
objExcel.ActiveSheet.PrintOut


Regards,
Tom Ogilvy

 

[joey]

Hey Tom, I tried the code and i get an error on line 5, any suggestions?

Thanks again

Joey