VBA Coding

  • Thread starter Thread starter Ken
  • Start date Start date
K

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 = fso.OpenTextFile(strInputFile, ForReading, True) 'Text
to be Read
Set strOutputFSO = fso.OpenTextFile(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
 
Jargon??? Sorry that's nip picking and I do not have any time for that.
However, I was able to solve this challenge

Thanks
 
Back
Top