Ask Question Forum:

Model Library:2025-02-08 Updated:A.I. model including DeepSeek is online for auto reply question page

Show

ASK RECENT

VBS to Convert csv file to excel file

Asked by davidam

at 2024-05-05 12:02:56

Point:500 Replies:32 POST_ID:828499USER_ID:11331

Topic:

Visual Basic Programming;VB Script;Microsoft Excel Spreadsheet Software

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 xlAppDim xlBookDim FSOSet xlApp = CreateObject("Excel.Application")xlApp.Visible = FalseSet FSO = CreateObject("Scripting.FileSystemObject")Dim oBookToProcessstrComputer = "."strDirToMonitor = "C:servrincoming"strProcDir = "C:servrcalculate"strTime = "20"Set objWMIService = GetObject("winmgmts:" & strComputer & "
ootcimv2")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 NextDo	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 IfLoop

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:

Select All Code

Expert: RobSampson replied at 2024-05-11 03:30:46

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.

Expert: RobSampson replied at 2024-05-08 20:01:07

Thanks for the grade.

Regards,

Rob.

Accepted Solution

Expert: RobSampson replied at 2024-05-08 16:06:56

500 points EXCELLENT

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

1:

Select All Code

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

1:2:3:4:5:6:7:8:9:

Select All Code

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

1:2:3:4:5:6:7:8:9:10:

Select All Code

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, TrueEnd Sub

1:2:3:4:5:6:7:8:9:10:

Select All Code

and I think that should work.

Regards,

Rob.

Author: davidam replied at 2024-05-07 12:09:47

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 xlAppDim objFSODim oBookToProcessConst xlUp = -4162Const xlDown = -4121Const xlValues = -4163Set xlApp = CreateObject("Excel.Application")Set objFSO = CreateObject("Scripting.FileSystemObject")strComputer = "."strDirToMonitor = "C:srvrfilesin"strProcessingDir = "C:srvrProcessing""at this point code sets up the monitoring of C:srvrfilesin  for an __InstanceCreationEvent (a file is transferred in)"DoErr.ClearSet 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 IfNext "Call a procedure that is expecting an xls file in strProcessingDir"End If"close text file"Loop

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:

Select All Code

Expert: zorvek (Kevin Jones) replied at 2024-05-07 12:04:59

I know. You're good. We just have to be strict on that rule because others try to abuse the points.

Author: davidam replied at 2024-05-07 12:02:24

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

Expert: zorvek (Kevin Jones) replied at 2024-05-07 11:57:35

davidam,

Please do not ask duplicate questions. Wait until this one has played out. You are currently getting help from two experts.

We do not allow duplicate questions because of a site rule that experts can only earn a maximum of 500 points per question.

zorvek
Excel Zone Advisor

Author: davidam replied at 2024-05-07 08:03:24

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?

Expert: leonstryker replied at 2024-05-06 20:37:04

You should be putting a path and a name there.

Author: davidam replied at 2024-05-06 17:05:21

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

Expert: duncanb7 replied at 2024-05-06 07:30:32

Anyway, glad to see you got the final solution.

Expert: leonstryker replied at 2024-05-06 06:48:39

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.

Expert: duncanb7 replied at 2024-05-05 22:35:28

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.

Expert: duncanb7 replied at 2024-05-05 22:29:30

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,

Author: davidam replied at 2024-05-05 22:12:33

Well, it loaded when I removed the Filename:= and just used SaveAs...

Author: davidam replied at 2024-05-05 19:35:24

Tried it , it will not load. Char 21; Expected statement; 800A0400

Expert: leonstryker replied at 2024-05-05 18:59:10

Try this:

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

Author: davidam replied at 2024-05-05 18:17:33

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

1:2:3:4:5:6:

Select All Code

Author: davidam replied at 2024-05-05 16:20:26

I am thinking maybe the SaveAs needs a path?

Author: davidam replied at 2024-05-05 15:37:46

I am trying everything I can think of on the syntax, but it always errors on the SaveAs line

Author: davidam replied at 2024-05-05 15:09:03

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

1:2:3:4:5:6:

Select All Code

Expert: duncanb7 replied at 2024-05-05 14:40:11

Why it is xlOpenXMLWorkbook or not xlCSV if you want save it in csv format

Author: davidam replied at 2024-05-05 14:24:35

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

1:2:3:4:5:

Select All Code

Author: davidam replied at 2024-05-05 13:36:47

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.NextEventIf 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

1:2:3:4:5:6:7:8:9:10:

Select All Code

Expert: duncanb7 replied at 2024-05-05 13:29:00

Probably it is easy to solve
So, I leave here , bye bye

Expert: SiddharthRout replied at 2024-05-05 13:23:58

A small typo.

Please change line 9 in my code to

wb1.SaveAs OutputFile, xlnormal

1:

Select All Code

Sid

Expert: SiddharthRout replied at 2024-05-05 13:18:28

xover

Sid

Expert: SiddharthRout replied at 2024-05-05 13:18:02

Paste this in a notepad and save it as a vbs

Const xlnormal = -4143Const InputFile = "E:UsersSiddharth RoutDesktopcodes.Csv"Const OutputFile = "E:UsersSiddharth RoutDesktopMyFile.xls"Dim oXL, wb1Set oXL = CreateObject("Excel.Application")Set wb1 = oXL.Workbooks.Open(InputFile)wb1.SaveAs OutputFile, xlsnormalwb1.Close SaveChanges = FalseSet wb1 = NothingoXL.QuitSet oXL = Nothing

1:2:3:4:5:6:7:8:9:10:11:12:13:14:

Select All Code

Sid

Expert: leonstryker replied at 2024-05-05 13:16:56

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

Expert: duncanb7 replied at 2024-05-05 13:06:07

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

Author: davidam replied at 2024-05-05 12:56:29

That is what I want to do...what changes do I have to make to accomplish this?

Expert: leonstryker replied at 2024-05-05 12:49:47

The question is why? Just open the CSV file in Excel, and save it as an Excel file.

&nbps;

About Us|Service|Pricing

即時新聞:↓

即時新聞:↑

On Balance Volume (OBV):

Ask Question Forum:

VBS to Convert csv file to excel file

Video♬:↓

Your Information

Members/Profolio