PDA

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

GTO
05-02-2011, 04:07 AM
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?

GTO
05-02-2011, 05:08 AM
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.