How to execute the ".bat" files using excel VBA !?

[CSLim]

Dear all,

I was created a macro VBA to run ".BAT" files so I can copy some documents with different formats into excel.
When I try on my ".BAT" files created by execute it manually was running fine without any problems.
However, I was failed to get my results when I tried to execute the ".BAT" files with excel macro VBA.

Can anyone helps me on this? Below is my VBA codes created.

Thanks in advance.

__________________________________________________________________________
Option Explicit

#If VBA7 Then
Private Declare PtrSafe Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long
#Else
Private Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Private Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long
#End If

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103

Sub LF_Click()
Dim BatFileName As String
Dim BatTxtTExcel As String
Dim NMFFileName As String
Dim XLSFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim DefPath As String
Dim BatPath As String
Dim Wb As Workbook
Dim oApp As Object
Dim oFolder
Dim foldername

'Create two temporary file names
BatFileName = DefPath & _
"\CollectNMFData" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
BatTxtTExcel = DefPath & _
"\NMFDataText2Excel" & Format(Now, "dd-mm-yy-h-mm-ss") & ".bat"
NMFFileName = DefPath & _
"\AllTXT" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "MasterTXT " & _
Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

'Path to save temp bat & files
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) <> "\" Then
DefPath = DefPath & "\"
End If

'Browse for the NMF file needed
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select the NMF files", 512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) <> "\" Then
foldername = foldername & "\"
End If

'Declare a variable as a FileDialog object.
Dim fd As FileDialog
'Create a FileDialog object as a File Selection dialog box.
Set fd = Application.FileDialog(msoFileDialogOpen)

With fd
.FilterIndex = 1
.AllowMultiSelect = False
.InitialFileName = foldername 'Worksheets("NMF_Checking").Range("D2").Value
If .Show <> -1 Then
'The user pressed Cancel.
Set fd = Nothing
Exit Sub
End If
End With

BatPath = "C:\"
'Create the bat file
Open BatFileName For Output As #1
Print #1, "cd .." & BatPath
Print #1, "call ." & BatTxtTExcel & " " & fd.SelectedItems(1)
Close #1
Open BatTxtTExcel For Output As #2
Print #2, "@echo off"
Print #2, "copy %1 %1.xls > nul"
Print #2, "excel %1.xls"
Close #2

Dim RunBat As Double
Dim sYourCommand As String
sYourCommand = BatFileName
RunBat = Shell("cmd /c " & sYourCommand, vbNormalFocus)

End Sub
_______________________________________________________________________________________________​