PDA

View Full Version : Help me tweak working VBA Code



radneo
08-07-2014, 09:32 AM
I have several CSV files, each with a column of numbers (same size). The following block of code copies the columns from all the CSV files into a single workbook - where each column is copied to a new sheet in the workbook. Imagine a workbook with several sheets where each sheet is a column of numbers - this is the output.

The CSV files are named 01, 02,..., 032. Here is the problem: in the new workbook, the sheets are arranged as 01, 010, 011 and so on. The numerical order is ignored - I would like the sheets to be arranged as 01, 02, 03, and so on. Can someone please help me tweak this code to do so? Thank you very much!!



Sub test() Dim myDir As String, fn As String, wb As Workbook
Set wb = ActiveWorkbook
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then myDir = .SelectedItems(1) & "\"
End With
If myDir = "" Then Exit Sub
fn = Dir(myDir & "*.csv")
Do While fn <> ""
With Workbooks.Open(myDir & fn)
.Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count)
.Close False
End With
fn = Dir
Loop
End Sub

Bob Phillips
08-07-2014, 11:52 AM
Is this what you mean


Sub test()
Dim myDir As String, fn As String, wb As Workbook
Set wb = ActiveWorkbook
With Application.FileDialog(msoFileDialogFolderPicker)
If .Show Then myDir = .SelectedItems(1) & "\"
End With
If myDir = "" Then Exit Sub
fn = Dir(myDir & "*.csv")
Do While fn <> ""
With Workbooks.Open(myDir & fn)
.Sheets(1).Copy after:=wb.Sheets(wb.Sheets.Count)
wb.Sheets(wb.Sheets.Count).Name = Left$(fn, InstrRev(fn, ".")-1)
.Close False
End With
fn = Dir
Loop
End Sub

radneo
08-07-2014, 01:58 PM
Hi xld, thank you for replying. Unfortunately, it does not work - please look at the attached screenshot. I would like the sheets to be in order - 01, 02, 03, 04, 05, 06,....,032.

12071

Bob Phillips
08-07-2014, 02:17 PM
You will probably have to sort then. See http://www.cpearson.com/excel/sortws.htm

radneo
08-08-2014, 09:04 AM
thanks so much for the link! I will try it out.