How Can I Convert a Large Excel 2007 File to a Table Using Visual Basic 6?

Microsoft Excel 2007 doesn't handle tables very differently from older versions of Excel. Like Excel 2003, the program converts a range of cells to a table using Visual Basic. A new feature, called table styles, applies any of several formatting styles to the table you create. One you've converted the Excel file, edit the table style to further customize your table.

• 1

  In Excel, click "File" from the menu bar.

• 2

  Click "Options" from the left pane. The "Excel Options" window will open.

• 3

  Click "Customize Ribbon" from the left pane.

• 4

  Check the box next to "Developer" in the "Customize the Ribbon" section on the screen's right side. Click "OK."

• 5

  Switch to the "Developer" ribbon.

• 6

  Click "Visual Basic" from the "Code" tab. A Visual Basic window will open.

• 7

  Paste the following text into your Visual Basic file:

  Sub CreateTable()

  ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$F$100"), , xlYes).Name = _

  "Table1"

  End Sub

• 8

  Replace "$B$1:$F$100" with your actual data range.

• 9

  To assign your table a pre-made style, insert the following line before "End Sub"

  ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"

• 10

  Save the page. Close it.

• 11

  Click "Macros" from Excel's "Code" tab. The "Macro" window will open.

• 12

  Highlight "CreateTable." Click "Run."