+ Reply to Thread
Results 1 to 3 of 3

Export to Text File Starting Cell Point?

Hybrid View

mfleming Export to Text File Starting... 02-02-2011, 01:47 PM
mfleming Re: Export to Text File... 02-02-2011, 02:14 PM
mfleming Re: Export to Text File... 02-02-2011, 02:27 PM
  1. #1
    Registered User
    Join Date
    01-14-2011
    Location
    Manitoba
    MS-Off Ver
    Excel 2007
    Posts
    10

    Question Export to Text File Starting Cell Point?

    Hi.

    I currently modifying a module that will export the "columns" if the column includes text "Include" from Row 1.

    This works fine except I want it to start exporting at Row 3 rather than Row 2. No matter what I do it still starts exporting from Row 2. What am I doing wrong?

    Current Code"
    Public bForm As Boolean
    Private Sub Export_Click()
        
        ExportToTXT
        
    End Sub
    
    Sub ExportToTXT()
    '
    ' Exports the identified column to a Comma Delimited Text File
    ' Last Revised 2/2/2011
    '
        Dim iTab As Integer
        Dim iNumCols As Integer
        Dim iNumRows As Integer
        Dim iCol As Integer
        Dim iRow As Integer
        Dim sDestFolder As String
        Dim sNewFileName As String
        Dim sTextLine As String
        Dim bContinue As Boolean
    
        'Ask user to select the tabs
        frmSelectTabs.Show
        
        If bForm = False Then
            MsgBox "Selection canceled by user.", vbInformation
            Exit Sub
        End If
        
        sDestFolder = ""
        sDestFolder = SelectFolder()
        
        If sDestFolder = "" Then
            MsgBox "Selection canceled by user.", vbInformation
            Exit Sub
        Else
            For iTab = 0 To frmSelectTabs.lbTabs.ListCount - 1
            
                If frmSelectTabs.lbTabs.Selected(iTab) = True Then
                    Worksheets.Item(frmSelectTabs.lbTabs.List(iTab)).Activate
                    
                    ActiveSheet.UsedRange   'This resets the used range
                    ActiveSheet.UsedRange.Select
                    iNumCols = Selection.Columns.Count
                    iNumRows = Selection.Rows.Count
                    
                    'Check to make sure there that the current tab actually has columns to be include in the export
                    bContinue = False
                    For iCol = 1 To iNumCols
                        If InStr(1, UCase(Cells(1, iCol).Value), "INCLUDE") <> 0 Or _
                            InStr(1, UCase(Cells(1, iCol).Value), "EXPORT") <> 0 Then
                            bContinue = True
                        End If
                    Next iCol
    
                    If bContinue = True Then
                        sNewFileName = sDestFolder & ActiveSheet.Name
                        Open sNewFileName For Output As #1
    
                        For iRow = 1 To iNumRows  'Sets the Default starting location for exporting 1
                            sTextLine = ""
                            For iCol = 1 To iNumCols
                                If InStr(1, UCase(Cells(1, iCol).Value), "INCLUDE") <> 0 Or _
                                    InStr(1, UCase(Cells(1, iCol).Value), "EXPORT") <> 0 Then
                                    sTextLine = sTextLine & Cells(iRow, iCol).Value & ","
                                End If
                            Next iCol
                            sTextLine = Left(sTextLine, Len(sTextLine) - 1)
                            Print #1, sTextLine
                        Next iRow
                        Close #1
                        Range("A1").Select
                        MsgBox ("The tab titled '" & ActiveSheet.Name & "' did not contain any columns to be included" & vbCr & _
                            "in the file export and was skipped.")
                    End If
                End If
            Next iTab
    
        End If
        'Changes Active Workheet
        Worksheets(5).Activate
        Range("A1").Select
        
    End Sub
    Sample Excel File:
    See attached Image.

    Sample Output in text format:
    Enter Type Names:,parameter name##parameter type##unit##
    ,Enter Name##OTHER##
    type name1,value1
    type name2,value2
    This line should not be here:
    Enter Type Names:,parameter name##parameter type##unit##
    Attached Images Attached Images

  2. #2
    Registered User
    Join Date
    01-14-2011
    Location
    Manitoba
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Export to Text File Starting Cell Point?

    I was trying to test the different values but the Button was actually calling a different module.

    This fixed the row reference (which I thought it should in the first place)
    For iRow = 3 To iNumRows
    My next problem is it includes newlines in the text file even though I don't have anything in the cells.

    This is what is has: (there are over 2000 rows but I only included a few of the "," rows)
    ,Enter Name##OTHER##
    type name1,value1
    type name2,value2
    ,
    ,
    ,
    ,
    ,
    ,
    ,

  3. #3
    Registered User
    Join Date
    01-14-2011
    Location
    Manitoba
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Export to Text File Starting Cell Point?

    This statement is where it sets the selection of the rows.

    iNumRows = Selection.Rows.Count
    What is the best way to Select Row count only if Column A has actual values in it? Ie text of numbers not just black cells.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1