1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Save a range as text file using VBA

Discussion in 'VBA Macros' started by e_var, Mar 24, 2017.

  1. e_var

    e_var New Member

    Messages:
    20
    Yes I have seen and read and used... Chandoo save range as text VBA
    I have been using this method however, it provides me with different options. Here is the code
    Code (vb):
    Sub ExportRangetoFile()
    'Update 20130913
    Dim wb As Workbook
    Dim saveFile As String
    Dim WorkRng As Range
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set wb = Application.Workbooks.Add
    WorkRng.Copy
    wb.Worksheets(1).Paste
    saveFile = Application.GetSaveAsFilename(fileFilter:="Text Files (*.txt), *.txt")
    wb.SaveAs Filename:=saveFile, FileFormat:=xlText, CreateBackup:=False
    wb.Close
    Application.CutCopyMode = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    I have two questions.
    1. Is there a way I can save 'All' the data from a scrollable table, not just the visible?
    2. Is there a way I can separate the data from two or more tables? I can save the data by simply entering 'Table1:Table2' in the VB box, but there is not separation and it loses some of the formatting.
    I have included a dummy book, I am using the tables from the 'Update' worksheet.
    Thanks everyone!

    Attached Files:

    Chirag R Raval likes this.
  2. e_var

    e_var New Member

    Messages:
    20
    So I have been busting my brain to find a work-around... Haven't found it yet...

    I attempted to store the array as an object or string, so it would copy to the clipboard, and be accessible to 'ctrl + V' for other applications... no go

    What I don't understand is; The info is already on the clipboard, I can see it... why can't I paste it in another application ie. word, notepad, outlook ?? Since it is on the clipboard if a manually click on the data to paste in any workbook/worksheet I now have access to any other application???

    The recorded macro is simply ActiveSheet.Paste, but I can't seem to replicate the an instance via VBA that will have the same effect.

    Any help/insight??
  3. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,883
    Hi, e_var!

    That code is working fine, I think that you're entering the wrong table name.

    Let's see worksheet 'Follow Up'. There you can find tables Table6 and Table7 ranging from rows 9 to 18. If you run the macro and enter Table6 o Table7 you get only 10 row as these tables have only those 10 rows.

    If you enter Employee as table name you get 601 rows of that table located in 'Training Details' worksheet, with only the first 54 containing data.

    The dynamic tables Table6 and Table7 are fed from the table Employee by the slices that are at the left of them.

    Hope it helps.

    Regards!
    Monty likes this.
  4. e_var

    e_var New Member

    Messages:
    20
    Thanks for replying... sirjb7
    You are correct. What I was attempting to do is copy all the information from the scrollable tables. Table6 contains 37 records with only 10 showing. when I use the macro it only saves the 10 visible rows in table 6.
    Chirag R Raval likes this.
  5. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,883
    Hi, e_var!
    Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
    Regards!
    Chirag R Raval likes this.
  6. e_var

    e_var New Member

    Messages:
    20
    Sorry, I didn't solve it... still working on a solution... it's a slid a bit down on my priority list...

    Thanks for your assist
  7. SirJB7

    SirJB7 Excel Rōnin

    Messages:
    8,883
    Hi, e_var!
    If you didn't solve it when it climbs up a few steps in your priority ladder, please check again what posted in the comment of the uploaded file where you say that you uses the Update worksheet and no such one in that file.
    I suggest you to rebuild your upload file, describe what output you want, write it down in the sample file, and elaborate a bit on what should be done with the input data.
    Regards!
  8. e_var

    e_var New Member

    Messages:
    20
    Good catch, that WS is from a different WB. The dynamic tables are draw from the 'Details' WS.. Either way, the original problem exist. I can not copy all of the information from the dynamic tables listed on the 'Follow up' WS. Only what is visible.

    Thanks again
    Chirag R Raval likes this.

Share This Page