VBA Macro to combine CSV Files with Command Line copy function

Question

I've written a macro which enables files within a selected folder to be combined using the command shell. The reason I'm using the command prompt is that the files are large and take some time to open.

The problem is that when I pick a folder that contains a space (i.e. J:\Network Accrual\Invoices) the macro fails. I've attempted to follow other suggestions that include 4 quotations ("""" & path & """"), however this does not seem to for the operation I have set - "cmd.exe /C copy J:\TEST*csv J:\TEST\Combined.csv".

Here is the code:

Private Sub COMBINE_FILES()

Dim FldrPicker As FileDialog
Dim myPath As String

'Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

With FldrPicker
  .Title = "Select A Target Folder"
  .AllowMultiSelect = False
    If .Show <> -1 Then GoTo NextCode
    myPath = .SelectedItems(1) & "\"
End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Display path
MsgBox myPath

 'Combine files in Command Line
  Dim RET As Variant
  RET = Shell("cmd.exe /C copy  " & myPath & "*csv " & myPath & "Combine.csv", 0)

ResetSettings:
'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Tim Williams · Accepted Answer · 2015-09-11 00:24:59Z

1

Try

RET = Shell("cmd.exe /C copy  """ & myPath & "*csv"" """ & myPath & "Combine.csv""", 0)

answered Sep 11, 2015 at 0:24

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Macro to combine CSV Files with Command Line copy function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related