Welcome !
         

 trying to convert batch script to vba script

Author Message
robnye

  • Total Posts : 4
  • Scores: 0
  • Reward points : 0
  • Joined: 12/14/2016
  • Status: offline
trying to convert batch script to vba script Friday, January 06, 2017 2:23 AM (permalink)
0
Hello,

I have been working on calling a batch file from with a Access database report - using VBA

I completed this successfully, but now want to convert the script to run within the VBA code itself (this would help avoid the possibility of the batch file getting deleted)

Batch script - called from VB code with

Call shell(Environ$("COMSPEC") & " /c c:\Access\Rob\split_File.bat BTN", vbNormalFocus) ' run script to split the TT-NONSTG file into separate files
 @echo off cd c:\Access\Rob\TTFILS\%1  del TT-NONSTG-*.bak TT-NONSTG.old ren TT-NONSTG-*.txt TT-NONSTG-*.bak 	  for /f "tokens=1-16 delims=," %%d in ('type "TT-NONSTG.txt"') do ( >>"TT-NONSTG-%%g.txt" echo(%%d,%%e,%%f,%%g,%%h,%%i,%%j,%%k,%%l,%%m,%%n,%%o,%%p )  move /y TT-NONSTG.txt TT-NONSTG.old  

Ive worked out most of the commands for VB apart from the splitting of the files

VB code
Sub SplitFiles()
Dim DelVal As Variant
' delete old .bak files
'
ChDir "c:\Access\Rob\TTFILS\BTN"         'Change folder to desired folder
DelVal = Dir("*.bak")       'Get first file in folder
Do While Len(Nz(RetVal)) > 0   'Delete until no more files
  Kill PATHTOTTFILEbtn & DelVal
  RetVal = Dir()            'Get next file to rename
Loop

' delete old .old file
'
Kill PATHTOTTFILEbtn & "TT-NONSTG.old"

' rename old .txt files as .bak
'
Dim RetVal As Variant
RetVal = Dir("*.txt")       'Get first file in folder
Do While Len(Nz(RetVal)) > 0   'Rename until no more files
  Name RetVal As Left(RetVal, Len(RetVal) - 3) & "bak"  'Rename
  RetVal = Dir()            'Get next file to rename
Loop

' split the original file
'
for /f "tokens=1-16 delims=," %%d in ('type "TT-NONSTG.txt"') do (
>>"TT-NONSTG-%%g.txt" echo(%%d,%%e,%%f,%%g,%%h,%%i,%%j,%%k,%%l,%%m,%%n,%%o,%%p
)

' rename non split file to .old
'
Name PATHTOTTFILEbtn & "TT-NONSTG.txt" As PATHTOTTFILEbtn & "TT-NONSTG.old"

End Sub

 
I admit some of the VB code is a little long winded, but I know it works as used in the past
the code to split the files I am having trouble with
 
 
#1
    Online Bookmarks Sharing: Share/Bookmark

    Jump to:

    Current active users

    There are 0 members and 2 guests.

    Icon Legend and Permission

    • New Messages
    • No New Messages
    • Hot Topic w/ New Messages
    • Hot Topic w/o New Messages
    • Locked w/ New Messages
    • Locked w/o New Messages
    • Read Message
    • Post New Thread
    • Reply to message
    • Post New Poll
    • Submit Vote
    • Post reward post
    • Delete my own posts
    • Delete my own threads
    • Rate post

    2000-2017 ASPPlayground.NET Forum Version 3.9