Excel – How to read data from Excel Sheet using Excel VBA 2010

microsoft-excel-2010vba

While in one Excel sheet I would like to read data from another Excel sheet and write it to a text file.

Example of the first sheet:

Sheet_Name   File_Name    Start_Line   End_Line
Sheet1       C:\Test.txt  B5           H10         Button

Clicking the button should write Sheet1 cells B5-H10 to C:\Test.txt.

How to do that?

Best Answer

Here's an example of how to handle a text file directly

Private Sub CommandButton1_Click()
    Dim shSrc As Worksheet
    Dim rSrc As Range
    Dim dat As Variant
    Dim FileNumber As Integer
    Dim rw As Long, cl As Long

    Set shSrc = Worksheets(Me.Cells(2, 1).Value)
    dat = shSrc.Range(Me.Cells(2, 3).Value & ":" & Me.Cells(2, 4).Value).Value

    FileNumber = FreeFile
    Open Me.Cells(2, 2).Value For Output As #FileNumber
    For rw = 1 To UBound(dat, 1)
    For cl = 1 To UBound(dat, 2)
        Print #FileNumber, dat(rw, cl)
    Next cl, rw
    Close #FileNumber
End Sub

Note:

  1. Assumes an ActiveX button
  2. You will need to add error handling
  3. You don't say what layout you want in the text file. This justs write one cell per line.
Related Question