View Full Version : Solved: A code to write TXT file
khaledocom
05-02-2011, 03:52 AM
Hi gents,
I need a code through Excel vba to make a text file as follows:
Body:
on error resume next
dim ExcelApp, ExcelWB
set ExcelApp = createobject("Excel.Application")
ExcelApp.visible = true
set ExcelWB = ExcelApp.Workbooks.Open("d:\fleet\monthlySalesReport.xls")
ExcelApp.AutoRecover.Enabled = False
wscript.quit
Then to save it as:
SalesReportReminder.vbs
In below Location:
C:\Sales
Hi gents,
I need a code through Excel vba to make a text file as follows:
Just to make sure I understand... you want the procedure in vba to create a .vbs file, is that correct?
khaledocom
05-02-2011, 04:47 AM
Yes, my friend.. Is it possible?
Sure. I did not test the result code, but would suggest that it checks for the existence of the workbook, rather than skipping by the error if not.
Option Explicit
Sub CreateScript()
Dim FSO As Object
Dim fsoStream As Object
Dim Path As String
'//Change path to suit, include trailing seperator
Path = ThisWorkbook.Path & "\"
Set FSO = CreateObject("Scripting.FileSystemObject")
If FSO.FolderExists(Path) Then
Set fsoStream = FSO.CreateTextFile(Path & "SalesReportReminder.vbs", True, False)
fsoStream.Write _
"Dim ExcelApp, ExcelWB" & vbCrLf & _
String(4, Chr(32)) & "On Error Resume Next" & vbCrLf & _
String(4, Chr(32)) & "Set ExcelApp = CreateObject(""Excel.Application"")" & vbCrLf & _
String(4, Chr(32)) & "ExcelApp.Visible = True" & vbCrLf & _
String(4, Chr(32)) & "Set ExcelWB = ExcelApp.Workbooks.Open(""d:\fleet\monthlySalesReport.xls"")" & vbCrLf & _
String(4, Chr(32)) & "ExcelApp.AutoRecover.Enabled = False" & vbCrLf & _
String(4, Chr(32)) & "WScript.Quit"
fsoStream.Close
End If
End Sub
khaledocom
05-02-2011, 08:58 AM
I tested, it's really gr8.
Thanks a lot bros.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.