VBA Coding

[Ken]

I'm using a coding to export a table as a CSV file and then excuting a
VBScript to read this CSV file and adding a record trailer and creating a new
file. Needless to say my coding is a failure i.e. it does not work.....

Can you please provide some suggestions in providing an efficient solution??

See below coding attempt:-

Private Sub Export_File_Click()
On Error GoTo Err_Export_File_Click

Dim StrDocName As String
Dim stQryName As String
Dim SpecName As String
Dim TableName As String
Dim OutputName As String
Dim MyDir As String

MyDir = CodeProject.path

Forms!FrmMainMenu!Message3.Caption = " " ' Display In
Progress message
Forms!FrmMainMenu!Message3.Caption = "In Progress!!!" ' Display In
Progress message

stQryName = "qryDelExportFile"
DoCmd.OpenQuery stQryName, , acReadOnly ' Delete all records in Export
file

stQryName = "qryAppExportFile"
DoCmd.OpenQuery stQryName, , acReadOnly ' Add Selected Records to Export
file

Forms!FrmMainMenu!Message3.Caption = "Completed" ' Display In Progress
message

SpecName = "TblWorkBenchInterface Export Specification"
TableName = "tblWorkBenchInterface"
OutputName = MyDir + "\COBSCO.CSV"

DoCmd.TransferText acExportDelim, SpecName, TableName, OutputName, True
'Create Data file

'Add Trailer into Text File

CmdString = "WScript " + MyDir + "\Export.vbs >C:Output.txt" 'Execute
BATCH File

Shell CmdString, 1



Exit_Export_File_Click:
Exit Sub

Err_Export_File_Click:
MsgBox CmdString
MsgBox Err.Description
Resume Exit_Export_File_Click
End Sub
____________________________________________________
VBSCRIPT

Const ForWriting = 2
Const ForReading = 1
Const ForAppending = 8
Const TristateFalse = 0

On Error Resume Next

Set objShell = CreateObject("Wscript.Shell")
Set objNetwork = CreateObject("Wscript.Network")
Set fso = CreateObject("Scripting.FileSystemObject")
If err.Number <> 0 Then
WScript.Echo (Now & " - Failed to create FileSystem Object!")
WScript.Echo (Now & " - Description: " & Err.Description)
WScript.Echo (Now & " - Error Source: " & Err.Source)
WScript.Quit(255)
End If

'Create File Name
strMonth = Month(Now)

If Len(strMonth) = 1 Then
strMonth = "0" & strMonth
End If

strDay = Day(Now)

If Len(strDay) = 1 Then
strDay = "0" & strDay
End If

strInputFile = "COBSCO.CSV"
strOutputFile = "COBSCO" & strDay & strMonth & Year(Now) & ".CSV"

Set strInputFile = fspenTextFile(strInputFile, ForReading, True) 'Text
to be Read
Set strOutputFSO = fspenTextFile(strOutputFile, ForAppending, True) 'Output

Do while strInputFile.AtEndofStream <> True
theLine = strInputFile.ReadLine
strOutputFSO.Writeline theLine
Loop

theLine = "END"
strOutputFSO.Writeline theLine
'Close Procedure
strInputFile.close
strOutputFSO.close
Wscript.Close

 

[Chris O'C via AccessMonster.com]

That's like saying "I'm using denominations for remunerations." Everyone
goes "Huh?"

What you mean is you're using a vba procedure to export your table and
vbscript to copy it and append text to the new file. Learning the jargon is
pretty easy, eh?

You say your code doesn't work, but you didn't say how it's failing or even
where it's failing.

Why not simplify it with a batch file? Get rid of the complex vbscript file.
Put these 2 lines in a batch file named addnewline.bat and save it in the
same folder as your code project:

copy COBSCO.CSV %1
echo END >> %1

In your vba procedure replace these 2 lines:

CmdString = "WScript " + MyDir + "\Export.vbs C:Output.txt" 'Execute BATCH
File
Shell CmdString, 1

with this:

Shell CodeProject.path & "\addnewline.bat COBSCO" & _
Format(Day(Date), "00") & Format(Month(Date), "00") & Year(Date) & ".
csv", vbHide


Chris

 

[Chris O'C via AccessMonster.com]

Looks like the editor messed up the formatting. I'll try again.

Shell CodeProject.Path & "\addnewline.bat COBSCO" _
& Format(Day(Date), "00") & Format(Month(Date), "00") _
& Year(Date) & ".csv", vbHide

Chris

 

[Ken]

Jargon??? Sorry that's nip picking and I do not have any time for that.
However, I was able to solve this challenge

Thanks