• 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...

  • 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

e_var

New Member
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:
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!
 

Attachments

  • dmy VTT v1.3.3.xlsm
    326.5 KB · Views: 8
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??
 
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!
 
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.
 
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
 
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!
 
Hi, e_var!
.... 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.
...
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
 
Back
Top