Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VBS to Convert csv file to excel file

Posted on 2011-05-05
32
Medium Priority
?
1,106 Views
Last Modified: 2012-05-11
Hello Experts,
We are using vbs to monitor a directory for incoming excel files and then perform certain operartions on those files.  We would like to change it to monitor for csv files and then, upon receiving a csv file, have vbs convert it to an excel file.  Attached is some of the code we now have.  How can we do this?
Dim xlApp				
Dim xlBook				
Dim FSO				
Set xlApp = CreateObject("Excel.Application")		
xlApp.Visible = False				
Set FSO = CreateObject("Scripting.FileSystemObject")	
Dim oBookToProcess				
strComputer = "."					
strDirToMonitor = "C:\servr\incoming"		
strProcDir = "C:\servr\calculate\"		
strTime = "20"					
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")				
Set colMonitoredEvents = objWMIService.ExecNotificationQuery _		
    ("SELECT * FROM __InstanceOperationEvent WITHIN " & strTime & " WHERE " _				
        & "Targetinstance ISA 'CIM_DirectoryContainsFile' and " _				
            & "TargetInstance.GroupComponent= " _	
                & "'Win32_Directory.Name=" & Chr(34) & Replace(strDirToMonitor, "\", "\\\\") & Chr(34) & "'")	
On Error Resume Next				
Do				
	Err.Clear			
	Set objLatestEvent = colMonitoredEvents.NextEvent			
	If objLatestEvent.Path_.Class = "__InstanceCreationEvent" Then			
		For Each objFile In FSO.GetFolder(strDirToMonitor).Files		
			If Right(LCase(objFile.Name), 4) = ".xls" Then	
				FSO.MoveFile objFile.Path, strProcDir & "\"
				End If
			End If	
		Next		
		Call Next_Procedure		
	End If			
Loop

Open in new window

0
Comment
Question by:davidam
  • 13
  • 6
  • 5
  • +2
30 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 35701376
The question is why? Just open the CSV file in Excel, and save it as an Excel file.
0
 

Author Comment

by:davidam
ID: 35701432
That is what I want to do...what changes do I have to make to accomplish this?
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 35701498
You want convert Excel to CSV or CSV to EXCEL ?

convert Excel to CVS as follows
========================
ActiveWorkbook.SaveAs filename:=jspfilename, _
        FileFormat:=xlCSV, ReadOnlyRecommended:=False, CreateBackup:=False
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 29

Expert Comment

by:leonstryker
ID: 35701565
You are already instancing Excel

Set xlApp = CreateObject("Excel.Application")      

So

Dim wkb As Object
Set wkb = xlApp.Workbooks.Open(myCVSfile)
wkb.SaveAs Filename:= myXLSfile, FileFormat :=xlOpenXMLWorkbook, CreateBackup:=False

Leon


0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35701577
Paste this in a notepad and save it as a vbs

Const xlnormal = -4143 
Const InputFile = "E:\Users\Siddharth Rout\Desktop\codes.Csv"
Const OutputFile = "E:\Users\Siddharth Rout\Desktop\MyFile.xls"

Dim oXL, wb1

Set oXL = CreateObject("Excel.Application")
Set wb1 = oXL.Workbooks.Open(InputFile)
wb1.SaveAs OutputFile, xlsnormal

wb1.Close SaveChanges = False
Set wb1 = Nothing
oXL.Quit
Set oXL = Nothing

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35701580
xover

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35701630
A small typo.

Please change line 9 in my code to

wb1.SaveAs OutputFile, xlnormal

Open in new window


Sid
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 35701666
Probably it is easy to solve
So, I leave here , bye bye
0
 

Author Comment

by:davidam
ID: 35701711
In terms of modifying my existing code I am thinking that I could change the .xls to .csv (in line 4)and then slip
Leon's line in there to just resave the file and then continue on with th rest of the code whcih is all looking for .xls files
Set objLatestEvent = colMonitoredEvents.NextEvent		
If objLatestEvent.Path_.Class = "__InstanceCreationEvent" Then	
	For Each objFile In FSO.GetFolder(strDirToMonitor).Files
		If Right(LCase(objFile.Name), 4) = ".xls" Then		Set wkb = xlApp.Workbooks.Open(myCVSfile)
  wkb.SaveAs Filename:= myXLSfile, FileFormat :=xlOpenXMLWorkbook, CreateBackup:=False
	         FSO.MoveFile objFile.Path, strProcDir & "\"	
		End If	
	    End If		
	Next			
    Call Next_Procedure

Open in new window

0
 

Author Comment

by:davidam
ID: 35702116
Please have a look at the attached code...it errors on the final line...expected statement at character 25
If Right(LCase(objFile.Name), 4) = ".cvs" Then
				If FSO.FileExists(strProcessingDir & "\" & objFile.Name) = True Then FSO.DeleteFile strProcessingDir & "\" & objFile.Name, True
				objLog.WriteLine Now & vbTab & "Moving " & objFile.Name & " to " & strProcessingDir & "\"
				Set wkb = xlApp.Workbooks.Open(objFile.Name)
				wkb.SaveAs Filename:=objFile.Name, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

Open in new window

0
 
LVL 13

Expert Comment

by:duncanb7
ID: 35702246
Why it is xlOpenXMLWorkbook or not xlCSV if you want save it in csv format
0
 

Author Comment

by:davidam
ID: 35702444
I am trying to go from csv to excel...the following errors on the last line  'expected statement'

For Each objFile In FSO.GetFolder(strDirToMonitor).Files
   Dim strFileName
     If Right(LCase(objFile.Name), 4) = ".cvs" Then
     strFileName = Left(objFile.Name, Len(objFile.Name)-4)
     Set wkb = xlApp.Workbooks.Open(objFile)
wkb.SaveAs Filename:=strFileName, xlsnormal

Open in new window

0
 

Author Comment

by:davidam
ID: 35702609
I am trying everything I can think of on the syntax, but it always errors on the SaveAs line
0
 

Author Comment

by:davidam
ID: 35702875
I am thinking maybe the SaveAs needs a path?
0
 

Author Comment

by:davidam
ID: 35703354
Any ideas?
For Each objFile In FSO.GetFolder(strDirToMonitor).Files
   Dim strFileName
          If Right(LCase(objFile.Name), 4) = ".csv" Then
             strFileName = Left(objFile.Name, Len(objFile.Name)-4)
            Set wkb = xlApp.Workbooks.Open(objFile)
          wkb.SaveAs Filename:=strDirToMonitor & "\" & strFileName & ".xls", FileFormat:=xlsnormal, CreateBackup:=False

Open in new window

0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35703465
Try this:

wkb.SaveAs Filename:=strDirToMonitor & "\" & strFileName & ".xls"
0
 

Author Comment

by:davidam
ID: 35703576
Tried it , it will not load.  Char 21; Expected statement; 800A0400
0
 

Author Comment

by:davidam
ID: 35703992
Well, it loaded when I removed the Filename:= and just used SaveAs...
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 35704035
1-It is xlNormal not  xlsnormal
2- Did Check the file is open or not ? if not, using CheckFileAvailable() to check it

3-Try this  
====
For Each objFile In FSO.GetFolder(strDirToMonitor).Files
   Dim strFileName as string
          If Right(LCase(objFile.Name), 4) = ".csv" Then
             strFileName = Left(objFile.Name, Len(objFile.Name)-4)
            Application.Workbooks.Open filename:="objfile.Name ", ReadOnly:=False
Do Until ActiveWorkbook.Name = "objfile.Name"
Loop
ActiveWorkbook.SaveAs filename:= strDirToMonitor & "\" & strFileName & ".xls", _
        FileFormat:=xlNormal, ReadOnlyRecommended:=False, CreateBackup:=False

========

4-Please make sure all file name correct you can use debug.print and Msgbox to read & verfiy it once .

5-Supposed all ojbfile in the folder with ".xls" extentsion as you know
Be careful coding at  "For Each objFile In FSO.GetFolder(strDirToMonitor).Files" , the file in the folder
might not be with ".xls" extentsion.


Be sure and check  Ojbfile could be opened or not in Excel before do anything
 
Everyday , I use such FSO , open, saveas file for more than 200 files, but no any issue.
If you send your example file and VBA code to us that will be easier to speed up solution,



0
 
LVL 13

Expert Comment

by:duncanb7
ID: 35704043
Be careful if you removed the Filename:=  even it works but not stable ,
if you do the code for many times and many files, you will see the error will happen in future that
I always experience a lot before.
Try to put it for safe, and solve the root cause out step by step, hurry is just final to wasting time.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35706245
Be careful if you removed the Filename:=  even it works but not stable

I originally forgot that this is being done in VBS, which means those param names can not be used. In VB or VBA they should be as it is much clearer and you do not have to relly on the order of the parameters, but it really has no effect on stability.
0
 
LVL 13

Expert Comment

by:duncanb7
ID: 35706591
Anyway, glad to see you got the final solution.
0
 

Author Comment

by:davidam
ID: 35710473
Am I supposed to put the name of the file in place of objfile.Name in the following line?
    Application.Workbooks.Open filename:="objfile.Name ", ReadOnly:=False
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35710867
You should be putting a path and a name there.
0
 

Author Comment

by:davidam
ID: 35712550
to leonstryker
When you say you do not use those parameters in vbs, are you referring only to the 'filename:=' or are you also referring to 'FileFormat:=xlNormal, ReadOnlyRecommended:=False, CreateBackup:=False' parameters?
0
 

Author Comment

by:davidam
ID: 35713138
Sorry, I was just trying to rekindle that earlier conversation...you can see that I referenced it and I was hoping that I could append it to the original...is that doable
0
 

Author Comment

by:davidam
ID: 35713156
Ok I see that I can still find all of the stuff I tried to post...
Here is the code converted into a full set of code. This code will load but it does not work.  I think  the problem lies in the fact that I need to have separate obects for the csv file and for the resulting xls file.  Also, for my application,  the resulting xls file needs to become  the same object as it would have been, if we were just passing through an xls file through. .

Dim xlApp
Dim objFSO
Dim oBookToProcess
Const xlUp = -4162
Const xlDown = -4121
Const xlValues = -4163
Set xlApp = CreateObject("Excel.Application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
strComputer = "."

strDirToMonitor = "C:\srvr\filesin\"
strProcessingDir = "C:\srvr\Processing\"
 
"at this point code sets up the monitoring of C:\srvr\filesin\  for an __InstanceCreationEvent (a file is transferred in)"

Do
Err.Clear
Set objLog = objFSO.OpenTextFile(strLogFile, ForAppending, True)
Set objLatestEvent = colMonitoredEvents.NextEvent
    If objLatestEvent.Path_.Class = "__InstanceCreationEvent" Then
    "write to txt file"
        For Each objFile In objFSO.GetFolder(strDirToMonitor).Files
        xlApp.DisplayAlerts = False
            If Right(LCase(objFile.Name), 4) = ".csv" Then
            strFilename = Left(objFile.Name, Len(objFile.Name)-4)
               If objFSO.FileExists(strProcessingDir & "\" & objFile.Name) = True Then objFSO.DeleteFile strProcessingDir & "\" & objFile.Name, True
                 objLog.WriteLine Now & vbTab & "Moving " & objFile.Name & " to " & strProcessingDir & "\"
                Application.Workbooks.Open objFile.Name
          ActiveWorkbook.SaveAs strProcessingDir & "\" & strFilename & ".xls"  
          If Err.Number <> 0 Then
          "write to txt file"
          Err.Clear
          End If
     End If
Next
 "Call a procedure that is expecting an xls file in strProcessingDir"
End If
"close text file"
Loop

Open in new window

0
 
LVL 65

Accepted Solution

by:
RobSampson earned 2000 total points
ID: 35716937
Hi guys,

I can see two issues wrong with what has currently been suggested.

1) As this is pure VBS, not VBA, you can't use the := qualifiers for the parameters.
2) As this is Excel 2003, you won't be able to use the xlOpenXMLWorkbook format, I believe you'll need to use the constant of xlWorkbookNormal with a value of 56

So, given that, try this out.  Add this to the top of your code:
Const xlWorkbookNormal = 56

Open in new window


Then change this section:

	If objLatestEvent.Path_.Class = "__InstanceCreationEvent" Then			
		For Each objFile In FSO.GetFolder(strDirToMonitor).Files		
			If Right(LCase(objFile.Name), 4) = ".xls" Then	
				FSO.MoveFile objFile.Path, strProcDir & "\"
				End If
			End If	
		Next		
		Call Next_Procedure		
	End If	

Open in new window


to this:
	If objLatestEvent.Path_.Class = "__InstanceCreationEvent" Then
		For Each objFile In objFSO.GetFolder(strDirToMonitor).Files
			If Right(LCase(objFile.Name), 4) = ".xls" Then
				FSO.MoveFile objFile.Path, strProcessingDir & "\"
			ElseIf Right(LCase(objFile.Name, 4)) = ".csv" Then
				Call Convert_File(objFile.Path)
			End If
		Next
		Call Next_Procedure
	End If

Open in new window


and then add this procedure to your code:
Public Sub Convert_File(strCSVFile)
	xlApp.DisplayAlerts = False
	Set oCSVToConvert = xlApp.Workbooks.Open(strCSVFile, False, False)
	strConvertedName = Left(strCSVFile, Len(strCSVFile) - 4) & ".xls"
	oCSVToConvert.SaveAs strConvertedName, xlWorkbookNormal
	oCSVToConvert.Close True
	xlApp.DisplayAlerts = True
	WScript.Sleep 1000
	FSO.DeleteFile strCSVFile, True
End Sub

Open in new window


and I think that should work.

Regards,

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35717459
Thanks for the grade.

Regards,

Rob.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 35736509
Sorry guys, I just had a chance to check this out on Excel 2003, and xlWorkbookNormal should have a value of -4143, not 56, and is also called xlNormal, so that can be changed if you want.

Regards,

Rob.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question