+ Reply to Thread
Results 1 to 8 of 8

Offset entire row help

Hybrid View

  1. #1
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Exclamation Offset entire row help

    Hi,

    I am trying to export each range of an excel document to txt.

    The information in column "A" is the title and column "B-D" is the information.

    Right now I can only get cell B to export with "Offset( , 1)"

    I tired using the & function to populate columns C and D but I am getting an object error.

    Any suggestions?

    Sub Export_Files()
        Dim sExportFolder, sFN
        Dim rArticleName As Range
        Dim rDisclaimer As Range
        Dim oSh As Worksheet
        Dim oFS As Object
        Dim oTxt As Object
    
        'sExportFolder = path to the folder you want to export to
        'oSh = The sheet where your data is stored
        sExportFolder = "C:\Users\A9C4VZZ\Desktop\Test"
        Set oSh = Sheet1
    
        Set oFS = CreateObject("Scripting.Filesystemobject")
        
             For Each rArticleName In oSh.UsedRange.Columns("A").Cells
                Set rDisclaimer = rArticleName.Offset(, 1) & "" & rArticleName.Offset(, 2) & rArticleName.Offset(, 3)
    
    
            'Add .txt to the article name as a file name
            sFN = rArticleName.Value & ".txt"
            Set oTxt = oFS.OpenTextFile(sExportFolder & "\" & sFN, 2, True)
            oTxt.Write rDisclaimer.Value
            oTxt.Close
        Next
       
    End Sub

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2505
    Posts
    27,224

    Re: Offset entire row help

    rDisclaimer is a range, and you are using Set to set it to a String. That won't work. Instead, make Disclaimer a string. See corrected code below in red.

    Sub Export_Files()
        Dim sExportFolder, sFN
        Dim rArticleName As Range
        Dim Disclaimer As String
        Dim oSh As Worksheet
        Dim oFS As Object
        Dim oTxt As Object
    
        'sExportFolder = path to the folder you want to export to
        'oSh = The sheet where your data is stored
        sExportFolder = "C:\Users\A9C4VZZ\Desktop\Test"
        Set oSh = Sheet1
    
        Set oFS = CreateObject("Scripting.Filesystemobject")
        
             For Each rArticleName In oSh.UsedRange.Columns("A").Cells
                Disclaimer = rArticleName.Offset(, 1) & "" & rArticleName.Offset(, 2) & rArticleName.Offset(, 3)
    
    
            'Add .txt to the article name as a file name
            sFN = rArticleName.Value & ".txt"
            Set oTxt = oFS.OpenTextFile(sExportFolder & "\" & sFN, 2, True)
            oTxt.Write Disclaimer
            oTxt.Close
        Next
       
    End Sub
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: Offset entire row help

    Hi Jeff,

    I updated the code but still am getting an "Object Required" error on the ".Offset"
    Sub Export_Files()
        Dim sExportFolder, sFN
        Dim rArticleName As Range
        Dim Disclaimer As String
        Dim oSh As Worksheet
        Dim oFS As Object
        Dim oTxt As Object
    
        'sExportFolder = path to the folder you want to export to
        'oSh = The sheet where your data is stored
        sExportFolder = "C:\Users\A9C4VZZ\Desktop\Test"
        Set oSh = Sheet1
    
        Set oFS = CreateObject("Scripting.Filesystemobject")
        
             For Each rArticleName In oSh.UsedRange.Columns("A").Cells
                Set Disclaimer = rArticleName.Offset(, 1) & "" & rArticleName.Offset(, 2) & "" & rArticleName.Offset(, 3)
    
    
            'Add .txt to the article name as a file name
            sFN = rArticleName.Value & ".txt"
            Set oTxt = oFS.OpenTextFile(sExportFolder & "\" & sFN, 2, True)
            oTxt.Write Disclaimer.Value
            oTxt.Close
        Next
    End Sub

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2505
    Posts
    27,224

    Re: Offset entire row help

    You did not change the code the way I showed. Use the exact same code that I posted. Make sure all the red code is an exact match.

  5. #5
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Question Re: Offset entire row help

    Yes, I did. I made Disclaimer as string and changed it in the other spots as well.

    Sub Export_Files()
        Dim sExportFolder, sFN
        Dim rArticleName As Range
        Dim Disclaimer As String
        Dim oSh As Worksheet
        Dim oFS As Object
        Dim oTxt As Object
    
        'sExportFolder = path to the folder you want to export to
        'oSh = The sheet where your data is stored
        sExportFolder = "C:\Users\A9C4VZZ\Desktop\Test"
        Set oSh = Sheet1
    
        Set oFS = CreateObject("Scripting.Filesystemobject")
        
             For Each rArticleName In oSh.UsedRange.Columns("A").Cells
                Set Disclaimer = rArticleName.Offset(, 1) & "" & rArticleName.Offset(, 2) & "" & rArticleName.Offset(, 3)
    
    
            'Add .txt to the article name as a file name
            sFN = rArticleName.Value & ".txt"
            Set oTxt = oFS.OpenTextFile(sExportFolder & "\" & sFN, 2, True)
            oTxt.Write Disclaimer.Value
            oTxt.Close
        Next
    Last edited by Rachel5694; 06-18-2019 at 06:33 PM.

  6. #6
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2505
    Posts
    27,224

    Re: Offset entire row help

    You are still using Set. Delete it.

    You are still using .Value. Delete it.

  7. #7
    Registered User
    Join Date
    06-28-2018
    Location
    US
    MS-Off Ver
    2016
    Posts
    89

    Re: Offset entire row help

    *Face Palm Emoji*

    I am so sorry, I definitely missed that part of it.

    Thank you so much for the help it worked!

  8. #8
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2505
    Posts
    27,224

    Re: Offset entire row help

    Glad it worked! Come back next time.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 11-13-2018, 04:54 AM
  2. Replies: 3
    Last Post: 06-01-2014, 10:26 AM
  3. Offset function not capturing entire range
    By shawnvon in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-09-2014, 03:09 AM
  4. function similar to offset to offset the offset
    By Jerseynjphillypa in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-03-2012, 03:07 PM
  5. Replies: 0
    Last Post: 02-14-2012, 12:34 PM
  6. Reformatting - insert formula with offset, then offset to next cell
    By Armitage2k in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-06-2011, 05:41 AM
  7. Offset Entire Row
    By mattydalton in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-09-2008, 12:05 PM

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