How to insert extra column

Question

the folowing code allows me to browse for multiple different excel files and paste them in a single sheet below each other.the excel file have the same column names but have different data in them and is working fine, my problem is i need it when it paste a file it must also create extra column and write the name of that file in that column for each and every file it paste.

Sub Button5_Click()
 Dim fileStr As Variant
 Dim wbk1 As Workbook, wbk2 As Workbook
 Dim ws1 As Worksheet

 fileStr = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xlsx), *.xlsx", Title:="Get File", MultiSelect:=True)
 Set wbk1 = ActiveWorkbook
 Set ws1 = wbk1.Sheets("Sheet3")

 'handling first file seperately
 MsgBox fileStr(1), , GetFileName(CStr(fileStr(1)))
 Set wbk2 = Workbooks.Open(fileStr(1))
 wbk2.Sheets(1).UsedRange.Copy ws1.Cells(ws1.Range("A" & Rows.Count).End(xlUp).Row + 2, 1)

 wbk2.Close

 For i = 2 To UBound(fileStr)
 MsgBox fileStr(i), , GetFileName(CStr(fileStr(i)))

 Set wbk2 = Workbooks.Open(fileStr(i))
 'using offset to skip the header - not the best solution,  but a quick one

 wbk2.Sheets(1).UsedRange.Offset(1, 0).Copy ws1.Cells(ws1.Range("A" & Rows.Count).End(xlUp).Row + 2, 1)

 wbk2.Close
 Next i
 End Sub

Olle Sjögren · Accepted Answer · 2012-10-16 13:40:44Z

1

Use the Insert method of a Range object to insert a column:

'***** Inserts new column to the left of column C
Range("C:C").Insert

Entering text in a cell:

'***** Entering text in A1
ws1.Cells(1, 1).Value = fileStr(i)

edited Oct 16, 2012 at 13:40

answered Oct 16, 2012 at 12:42

Olle Sjögren

5,3923 gold badges35 silver badges55 bronze badges

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

11 Comments

Cwala Over a year ago

We also looking for inserting file names in that newcolumn...and please we dont do hard coding.

Olle Sjögren Over a year ago

So where do you want the column? It was an example.

Cwala Over a year ago

at the end of each data that has been inserted

Olle Sjögren Over a year ago

I was under the impression that the column names and count were constant - in that case you know the last column. Are you pasting new data below the old or to the right of the old? (BTW your code is dependent on other code so I can't test it easily - GetFileName?)

Larry Over a year ago

If you do not want the fullpath, but the workbook name only, you can use wbk1.name/wbk2.name

|

Collectives™ on Stack Overflow

How to insert extra column

1 Answer 1

11 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

11 Comments

Your Answer

Sign up or log in

Post as a guest

Related