making a Macro that exports chosen columns to a new file

Tag: excel Author: kaoyanyc18 Date: 2009-08-15

What kind of Macro can be created that exports chosen columns to another new file?

for example exports columns A, B, C to a new file with a few new extra lines on top?

this would be a Button on page.

Other Answer1

This code will copy the selected range to a new workbook.

Sub Copy_Selection()

    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Application.CutCopyMode = False

End Sub

Once the macro has been added, it can be linked to a button.

For Excel 2007:

  • Click on the Developer tab
  • Use the Insert drop down to select the button from the Form controls section
  • The cursor will change to a cross hair
  • Left click, hold, drag and release on the screen to size the button
  • The Assign macro dialog will be displayed, select the macro and click the OK button
  • Right click on the button and select Edit text
  • Update the button text to 'Copy selection'

comments:

How can I define that columns A+B+D are copied ?
The code above assumes that the range has been selected by the user. If you want to target a specific range, add this code at the beginning: ActiveSheet.Range("A:A,B:B,D:D").Select

Other Answer2

You can use ADO. This example writes to a named sheet, but you could also write to a number of different applications, or a text document. It assumes that you have a header row with named columns (ColName1, ColName2) but it is also possible to copy columns that do not have a header row.

Dim cn As Object
Dim rs As Object

strFile = Workbooks(1).FullName
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile _
    & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon

strSQL = "SELECT ColName1, ColName2 FROM [Sheet4$]"

rs.Open strSQL, cn

For i = 0 To rs.Fields.Count - 1
    Sheets("Sheet2").Cells(1, i + 1) = rs.Fields(i).Name
Next

Sheets("Sheet2").Cells(2, 1).CopyFromRecordset rs

comments:

Thanks, looking for more simple solution, I dont want to work with OLE if possible