0

I am currently working on my first try on excel macros and probably not picked an easy start. I want to import specific columns from all .csv files in one folder (always the same column letter) into one excel sheet. So there are two columns of interest at the moment and I want to have one sheet for each csv column and all files as new excel columns. So 50 csv files with two .csv columns of interest would result into one excel file with two sheets and 50 columns each. I have tried a few things and the macro is currently looking like below. It is meant to pick up all .csv files from a directory, then select the column B from row 2 to number of rows used and copy this, then switch to the excel file and paste it into the next empty column where the header is set as the .csv filename and all data pasted below. Same shiuld be done for .cs column F. Afterwards the .csv file should be closed and the next one openend until files of the directory are processed. Currently the macro is failing in line 27: Windows(f.Name).Activate Giving 'runtime error 9 - index out of range'

Sub csv_imp()
'
' csv_imp Makro
'
' Tastenkombination: Strg+r
'

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")
Dim xl As New Excel.Application
Dim wb As New Excel.Workbook
Dim lastColumn
Dim folder
Dim rng As Range
Dim res As Workbook
Dim target As Range

Set folder = fso.getFolder("C:\data\")
Set res = Workbook("Results")
Set lastColumn = 0

For Each f In folder.Files
Debug.Print f.Name
If (f.Name Like "*.csv") Then
    Set rng = Range("B2:B60")
    rng.Copy
    Windows("Results").Activate
    Set wb = xl.Workbooks.Open(f.Path)
    wb.Sheets(1).Range(ColumnLetter(lastColumn + 1) & "1").Value = f.Name
    Set target = wb.Sheets(1).Range(ColumnLetter(lastColumn + 1) & "2")
    target.Paste
    lastColumn = lastColumn + 1
    wb.Save
    f.Close
End If

   Next
   xl.Quit
End Sub


Function ColumnLetter(ByVal ColumnNumber As Long) As String

   Dim n As Long
   Dim c As Byte
   Dim s As String
   n = ColumnNumber
Do
   c = ((n - 1) Mod 26)
   s = Chr(c + 65) & s
   n = (n - c) \ 26
   Loop While n > 0
   ColumnLetter = s

End Function

It would be great if someone could help :) Seb

Seb
  • 1
  • 2
  • What is wrong with your code? *"It is not running"* is no meaningful error description. Please describe what it actually does and what you expect it to do? What exactly is working and what is not working? Where do you get errors and which? • [“Can someone help me?” is not an actual question that we can answer](https://meta.stackoverflow.com/a/284237/3219613). – Pᴇʜ Jun 18 '18 at 09:01
  • Sorry for the poor description. I have made some edits to the original post and hope this clarifies the issue. – Seb Jun 18 '18 at 09:19
  • Start by eliminating your dependance on `Select`/`Activate`. [See here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/10717999#10717999) – chris neilsen Jun 18 '18 at 09:48
  • Thanks for the reply. As I mentioned, I am fairly new to macros in Excel. In my understanding select and activate are necessary to switch between the active files and get the values from the cells. Are you referring to giving input variables for selecting the .csv columns? Although I wouldn't know how to realise this. – Seb Jun 18 '18 at 10:13
  • @Seb did you see the link that Chris has in his comment? See: [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Especially if you are new to macros this should be one of the first tasks you should learn, because using `select` and `activate` makes your code slow and leads into many errors. – Pᴇʜ Jun 18 '18 at 11:54
  • Sorry I missed the link, thank you for the hint. I have avoided the use of select now and used objects instead. However, I do not really understand how to avoid activate when switching between two files. I have tried to create this code based on another example and I am actually not sure anymore that it does the right thing. In the middle wb is set to the csv files path and then the values are set there but should it not be the activated window Results instead? – Seb Jun 19 '18 at 13:07

0 Answers0