Results 1 to 8 of 8

Run export macro on different sized ranges in every worksheet with same starting cells

Threaded View

  1. #1
    Registered User
    Join Date
    01-04-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2010, 2013
    Posts
    17

    Run export macro on different sized ranges in every worksheet with same starting cells

    I have a macro that I run repeatedly in a certain type of spreadsheet that does an export on a selection, pulls up a "Save As" message box and saves as a text file. There are 9 sheets in the workbook that contain the same data analysis for a set of samples, the only difference between them being the number of data (e.g. some may have 1500, 1400, 1600, etc.). I only select three columns of data to export (I6:K????) that contain counts (1,2,3,....), x-coordinates and y-coordinates. The counts column (I) uses a formula that only counts if there is an x-coordinate next to it (Col J).

    =IF(ISBLANK(J6),"",ROW(J6)-5)
    Generally, what I do to make quick work out of selecting the variable ranges is to select Sheets 03-11 (the first nine sheets) and select K6:I6 (starting w/ K6). Then, on each individual sheet, I do CTRL+SHIFT+DOWN to select all the relevant data (if I started my selection with I6, then it would select all cells that contained formulas which may or may not have x,y-coordinates adjacent to them). Once the data is selected (I6:K????), I run the export macro and save the data as text. I would like a macro that can automate the selection for each Sheet 03-11, excluding Sheets "all", "data" and "summary", and run the export macro, first prompting me for a file location and a file prefix. When the text file is saved, it uses the file prefix and Worksheet name to build the filename, i.e. "path\prefix_wkshtname". I'd greatly appreciate anybody's help on this.

    I've attached an example workbook.
    Here are the export codes, which were taken from
    HTML Code: 
    :

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' ExportToTextFile
    ' This exports a sheet or range to a text file, using a
    ' user-defined separator character.
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Public Sub ExportToTextFile(FName As String, _
        Sep As String, SelectionOnly As Boolean, _
        AppendData As Boolean)
    
    Dim WholeLine As String
    Dim FNum As Integer
    Dim RowNdx As Long
    Dim ColNdx As Integer
    Dim StartRow As Long
    Dim EndRow As Long
    Dim StartCol As Integer
    Dim EndCol As Integer
    Dim CellValue As String
    
    
    Application.ScreenUpdating = False
    On Error GoTo EndMacro:
    FNum = FreeFile
    
    If SelectionOnly = True Then
        With Selection
            StartRow = .Cells(1).Row
            StartCol = .Cells(1).Column
            EndRow = .Cells(.Cells.Count).Row
            EndCol = .Cells(.Cells.Count).Column
        End With
    Else
        With ActiveSheet.UsedRange
            StartRow = .Cells(1).Row
            StartCol = .Cells(1).Column
            EndRow = .Cells(.Cells.Count).Row
            EndCol = .Cells(.Cells.Count).Column
        End With
    End If
    
    If AppendData = True Then
        Open FName For Append Access Write As #FNum
    Else
        Open FName For Output Access Write As #FNum
    End If
    
    For RowNdx = StartRow To EndRow
        WholeLine = ""
        For ColNdx = StartCol To EndCol
            If Cells(RowNdx, ColNdx).Value = "" Then
                CellValue = chr(34) & chr(34)
            Else
               CellValue = Cells(RowNdx, ColNdx).Value
            End If
            WholeLine = WholeLine & CellValue & Sep
        Next ColNdx
        WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
        Print #FNum, WholeLine
    Next RowNdx
    
    EndMacro:
    On Error GoTo 0
    Application.ScreenUpdating = True
    Close #FNum
    
    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' END ExportTextFile
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' DoTheExport
    ' This prompts the user for the FileName and the separtor
    ' character and then calls the ExportToTextFile procedure.
    ' http://www.cpearson.com/excel/ImpText.aspx
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sub DoTheExport()
        Dim Filename As Variant
        Dim Sep As String
        Filename = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files (*.txt),*.txt")
        If Filename = False Then
            ''''''''''''''''''''''''''
            ' user cancelled, get out
            ''''''''''''''''''''''''''
            Exit Sub
        End If
        Sep = vbTab
        '"vbTab" outputs selection as tab-delimited'
        'to choose separator, use'
        'Sep = Application.InputBox("Enter a separator character.", Type:=2)'
        
        If Sep = vbNullString Then
            ''''''''''''''''''''''''''
            ' user cancelled, get out
            ''''''''''''''''''''''''''
            Exit Sub
        End If
        Debug.Print "FileName: " & Filename, "Separator: " & Sep
        ExportToTextFile FName:=CStr(Filename), Sep:=CStr(Sep), _
           SelectionOnly:=True, AppendData:=False
    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' END DoTheExport
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Worksheet name ranges and how to make them reference cells in a worksheet
    By mammamia in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2011, 06:36 AM
  2. Comparing Two Different Sized Ranges For One Output
    By poleary2000 in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-17-2011, 01:51 PM
  3. Export to Text File Starting Cell Point?
    By mfleming in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2011, 02:27 PM
  4. Problem copying VBA to diff sized ranges
    By Mr Gonzalo in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-31-2007, 02:30 PM
  5. Starting a macro when switching to a worksheet?
    By Markus Mueller in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-30-2005, 08:40 AM

Tags for this Thread

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