Home
Search
 
What's New
Index
Books
Links
Q & A
Newsletter
Banners
 
Feedback
Tip Jar
 
C# Helper...
 
XML RSS Feed
Follow VBHelper on Twitter
 
 
MSDN Visual Basic Community
 
 
 
 
 
TitleLoad a CSV (comma-separated value) file in Excel and save it as an Excel spreadsheet
KeywordsCSV, Excel, spreadsheet, comma-separated value
CategoriesOffice
 
Create an Excel server. Use it to open the CSV file. AutoFit the cells and format the first row. Then save the file as a new Excel spreadsheet.
 
Private Sub cmdLoad_Click()
Dim excel_app As Excel.Application
Dim max_col As Integer

    Screen.MousePointer = vbHourglass
    DoEvents

    ' Create the Excel application.
    Set excel_app = CreateObject("Excel.Application")

    ' Uncomment this line to make Excel visible.
'    excel_app.Visible = True

    ' Load the CSV file.
    excel_app.Workbooks.Open _
        FileName:=txtFromFile.Text, _
        Format:=xlCSV, _
        Delimiter:=",", _
        ReadOnly:=True

    ' Autofit the columns.
    excel_app.ActiveSheet.UsedRange.Select
    excel_app.Selection.Columns.AutoFit

    ' Highlight the first row (column headers).
    max_col = excel_app.ActiveSheet.UsedRange.Columns.Count
    excel_app.ActiveSheet.Range( _
        excel_app.ActiveSheet.Cells(1, 1), _
        excel_app.ActiveSheet.Cells(1, max_col)).Select
    With excel_app.Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = 5
    End With

    ' Save as an Excel spreadsheet.
    excel_app.Workbooks(1).SaveAs txtExcelFile.Text, _
        xlExcel7

    ' Comment the rest of the lines to keep
    ' Excel running so you can see it.

    ' Close the workbook without saving.
    excel_app.ActiveWorkbook.Close False

    ' Close Excel.
    excel_app.Quit
    Set excel_app = Nothing

    Screen.MousePointer = vbDefault
    MsgBox "Ok"
End Sub
 
 
Copyright © 1997-2010 Rocky Mountain Computer Consulting, Inc.   All rights reserved.
  Updated