Ask Question Forum:
Model Library:2025-02-08 Updated:A.I. model is online for auto reply question page
C
O
M
P
U
T
E
R
2
8
Show
#
ASK
RECENT
←
- Underline
- Bold
- Italic
- Indent
- Step
- Bullet
- Quote
- Cut
- Copy
- Paste
- Table
- Spelling
- Find & Replace
- Undo
- Redo
- Link
- Attach
- Clear
- Code
Below area will not be traslated by Google,you can input code or other languages
Hint:If find spelling error, You need to correct it,1 by 1 or ignore it (code area won't be checked).
X-position of the mouse cursor
Y-position of the mouse cursor
Y-position of the mouse cursor
Testcursor
caretPos
Attachment:===
Asked by davidam
at 2024-05-05 12:02:56
ModifyPoint: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?
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:
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.
Regards,
Rob.
Expert: RobSampson replied at 2024-05-08 20:01:07
Thanks for the grade.
Regards,
Rob.
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:
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:
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:
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:
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:
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. .
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:
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
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?
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
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.
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.
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,
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"
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:
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:
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:
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
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:
Expert: duncanb7 replied at 2024-05-05 13:29:00
Probably it is easy to solve
So, I leave here , bye bye
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
Please change line 9 in my code to
Sid
Expert: SiddharthRout replied at 2024-05-05 13:18:28
xover
Sid
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:
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
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
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.
