Copy range and save as a new text file

[spycein]

Hello everyone,
I looking for a VBA macro code which would copy a specified column range and save the same as text file.
For example, when i click the command button the code will copy the assigned range and ask for the path & file name to save the file.
Regards,
Shib

 

[spycein]

Thank you so much @kennypete for all your help.
Actually i have two sheets, Sheet 1 and Sheet2.
In sheet2 i have following table

Date	Narration	Expense Register	Debit Ledger 1	GST Classification	Ledger Amount	GST Taxable Value	Cost Centre	Cost Centre Amount	Tax Rate	TDS Ledger Name	TDS Amount	Debit Ledger 2	Ledger Amount	Debit Ledger 3	Ledger Amount	Cr_Ledger	Ledger Amount
20200425	Test1	2020-21	Good Purchased	Purchase Taxable	- 200,000.00	-200000	2020	- 200,000.00	14	Not Applicable	- 200,000.00	VAT	- 28,000.00	GST	- 28,000.00	XYZ Company	256,000.00
20200420	Test2	2020-21	Good Purchased	Purchase Taxable	- 200,000.00	-200000	2020	- 200,000.00	14	Not Applicable	- 200,000.00	VAT	- 28,000.00	GST	- 28,000.00	ABC Company	256,000.00

And in Sheet1 column A i have concatenate the values with formula as follows

A1 :- 20200425Test12020-21Good PurchasedPurchase Taxable-200000-2000002020-20000014Not Applicable-200000VAT-28000GST-28000XYZ Company256000

A2:- 20200420Test22020-21Good PurchasedPurchase Taxable-200000-2000002020-20000014Not Applicable-200000VAT-28000GST-28000ABC Company256000

I have made this sheet1 as hidden. Only Sheet 2 is visible. i have also inserted a command button on Sheet2.

Now i am looking for a VBA code macro which copy the specific range( Column A concatenate formula populated) from Sheet1 without asking the user about the range and ask for the path & file name to save the file in text format.

I am really sorry to ask you to add prefix & suffix as it is not required in this scenario.

I hope i explained it properly.

Many Thanks for all your help.
Best Regards,

 

[kennypete]

Hi @spycein
Your requirements seem to be rather a "moving target". If you had said all you wanted to do is save column A as a .txt file (what happened to the .xml by the way?!) then that would have been a much easier request. This simplified code does that.
Assign this to a button on your Sheet2 and that should be it.

Sub Sheet1ColAtoTXT()
    Dim lngRow As Long
    Dim varSaveAsTxt As Variant
    Dim arrA As Variant
    
    ' NB: Hardcoded to "Sheet1" tab as requested!
    ' Now using an array to store the column's data as that is now a
    ' much easier to define range and it is quicker and simpler later
    arrA = Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
    
    varSaveAsTxt = Application.GetSaveAsFilename(FileFilter:="Text (Tab delimited) (*.txt), *.txt")
    If varSaveAsTxt = False Then Exit Sub
    
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile(varSaveAsTxt)
    
    For lngRow = 1 To UBound(arrA)
        oFile.writeline arrA(lngRow, 1)
    Next lngRow
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing

End Sub

 

[spycein]

Thank you so much @kennypepte.
But the code copies data from the selected sheet only and does not copy the range from sheet1.
Means my data entry template is on sheet2 and once i run the macro after populating the information, the text file generated and copies the data from sheet2 column A only.

Date	Narration	Expense Register	Debit Ledger 1	GST Classification	Ledger Amount	GST Taxable Value	Cost Centre	Cost Centre Amount	Tax Rate	TDS Ledger Name	TDS Amount	Debit Ledger 2	Ledger Amount	Debit Ledger 3	Ledger Amount	Cr_Ledger	Ledger Amount
20200425	Test1	2020-21	Good Purchased	Purchase Taxable	- 200,000.00	-200000	2020	- 200,000.00	14	Not Applicable	- 200,000.00	VAT	- 28,000.00	GST	- 28,000.00	XYZ Company	256,000.00
20200420	Test2	2020-21	Good Purchased	Purchase Taxable	- 200,000.00	-200000	2020	- 200,000.00	14	Not Applicable	- 200,000.00	VAT	- 28,000.00	GST	- 28,000.00	ABC Company	256,000.00

For example, if i run the macro based on the above data then the txt file gives me following output file

Date
20200425
20200420

if i unhide the sheet1 and run the macro then only the txt file gives me proper output file

20200425Test12020-21Good PurchasedPurchase Taxable-200000-2000002020-20000014Not Applicable-200000VAT-28000GST-28000XYZ Company256000
20200420Test22020-21Good PurchasedPurchase Taxable-200000-2000002020-20000014Not Applicable-200000VAT-28000GST-28000ABC Company256000

As i mentioned earlier my sheet1 is hidden and i dont want to make the sheet visible to others.

I hope i explained it properly.

Many Thanks for all your help.
Best Regards,

 

[kennypete]

Sure, since it's hidden, unhide it and rehide it within the code. Use the revised version below, which is commented to say what it's doing.

Correspondingly, would you please answer the question I had regarding where the XML output you talked of disappeared to.

Thanks, and I hope this finalises it.

Sub Sheet1ColAtoTXT()
    Dim strStartingSheet As String
    Dim lngRow As Long
    Dim varSaveAsTxt As Variant
    Dim arrA As Variant
     
    strStartingSheet = ActiveSheet.Name
    ' Turn off showing the screen
    Application.ScreenUpdating = False
    
    ' NB: Hardcoded to "Sheet1" tab as requested!
    Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Select
    ' Now using an array to store the column's data as that is now a
    ' much easier to define range and it is quicker and simpler later
    arrA = Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
    
    ' Reselect the starting sheet before rehiding Sheet1
    Sheets(strStartingSheet).Select
    Sheets("Sheet1").Visible = False
    
    ' Turn on showing the screen
    Application.ScreenUpdating = True
    
    varSaveAsTxt = Application.GetSaveAsFilename(FileFilter:="Text (Tab delimited) (*.txt), *.txt")
    If varSaveAsTxt = False Then Exit Sub
    
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile(varSaveAsTxt)
    
    For lngRow = 1 To UBound(arrA)
        oFile.writeline arrA(lngRow, 1)
    Next lngRow
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing

End Sub

 

[spycein]

Thank you so much @kennypete.
The code works like a charm. Finally got the result which i wanted.
Cannot thank you more.
Regarding the output file save in xml format, i also realised that both the format (i.e. text & xml) work to import the data in the third party software.
Thats why i stick to text format only.

Many many thanks once again.
Best Regards,

 

[kennypete]

Happy that this worked out. Cheers

 

[spycein]

Sure, since it's hidden, unhide it and rehide it within the code. Use the revised version below, which is commented to say what it's doing.

Correspondingly, would you please answer the question I had regarding where the XML output you talked of disappeared to.

Thanks, and I hope this finalises it.

Sub Sheet1ColAtoTXT()
    Dim strStartingSheet As String
    Dim lngRow As Long
    Dim varSaveAsTxt As Variant
    Dim arrA As Variant
   
    strStartingSheet = ActiveSheet.Name
    ' Turn off showing the screen
    Application.ScreenUpdating = False
  
    ' NB: Hardcoded to "Sheet1" tab as requested!
    Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Select
    ' Now using an array to store the column's data as that is now a
    ' much easier to define range and it is quicker and simpler later
    arrA = Range("A1:A" & Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row)
  
    ' Reselect the starting sheet before rehiding Sheet1
    Sheets(strStartingSheet).Select
    Sheets("Sheet1").Visible = False
  
    ' Turn on showing the screen
    Application.ScreenUpdating = True
  
    varSaveAsTxt = Application.GetSaveAsFilename(FileFilter:="Text (Tab delimited) (*.txt), *.txt")
    If varSaveAsTxt = False Then Exit Sub
  
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile(varSaveAsTxt)
  
    For lngRow = 1 To UBound(arrA)
        oFile.writeline arrA(lngRow, 1)
    Next lngRow
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing

End Sub

Happy that this worked out. Cheers

Dear @kennypete, Need your help on the above code.
With the existing code i can copy and save the data from "Column A" only.
Could you please help to copy the data from more than once Column i.e. say Column "A to D" and CONCATENATE the value in a single line and save it as xml file.
For example if the value in Column A is 1A, Column B is 2A, Column C is 3A, Column D is 4A etc.. then my output result would be 1A2A3A4A.
The Column range might get expanded from "Column A" to "Column J".
Thank you so much in advance.
Best Regards,

 

[kennypete]

Dear @kennypete, Need your help on the above code.
With the existing code i can copy and save the data from "Column A" only.
Could you please help to copy the data from more than once Column i.e. say Column "A to D" and CONCATENATE the value in a single line and save it as xml file.
For example if the value in Column A is 1A, Column B is 2A, Column C is 3A, Column D is 4A etc.. then my output result would be 1A2A3A4A.
The Column range might get expanded from "Column A" to "Column J".
Thank you so much in advance.
Best Regards,

Something like this will work:

Sub ChosenRangeSaveAsXML()

    'strStartingSheet = ActiveSheet.Name
    ' Turn off showing the screen
    'Application.ScreenUpdating = False
  
    ' NB: Hardcoded to "Sheet1" tab as requested!
    'Sheets("Sheet1").Visible = True
    Sheets("Sheet1").Select
    
    strRange = InputBox("Enter range to save (e.g. A1:A3, A1:E5, etc)", "Range Selection", "A1:A1")
  
    ' Reselect the starting sheet before rehiding Sheet1
    'Sheets(strStartingSheet).Select
    'Sheets("Sheet1").Visible = False
    
    ' Turn on showing the screen
    'Application.ScreenUpdating = True
  
    varSaveAsTxt = Application.GetSaveAsFilename(FileFilter:="Text (Tab delimited) (*.txt), *.txt")
  
    Dim fso As Object
    Set fso = CreateObject("Scripting.FileSystemObject")
    Dim oFile As Object
    Set oFile = fso.CreateTextFile(varSaveAsTxt)
  
    strAll = ""
    For Each c In Range(strRange)
        strAll = strAll & c.Value
    Next c
    oFile.writeline strAll
    oFile.Close
    Set fso = Nothing
    Set oFile = Nothing

End Sub

Note I only focused on the range choice and saving that range as a concatenated single string as you have requested.

 

[spycein]

Hi @kennypete , Thank you so much for the help. But code copies the range from the selected sheet not from the hard-coded sheet.
For example if i am on sheet3 and run the macro then Input box appear and ask for the range. Once i mention the range and save the file then the values get copied from Sheet3 not Sheet.
My source data is in Sheet1 which need to be copied and saved. I dont want my user to access the sheet one, that's why i have kept the sheet as xlveryhidden. I want my user to generate the text file only that is why i put the macro button on Sheet3.
Could you please hardcode the range with sheet1 instead of Input Box? That would be a great help for me.
I hope i explained my query properly. Looking forward for your help and solution.
Many Thanks.
Best Regards

 

[kennypete]

Sorry, no, I don't get it. How are you to know what range on Sheet 1 to output? You say:
"Could you please help to copy the data from more than once Column i.e. say Column "A to D" and CONCATENATE the value in a single line and save it as xml file.
For example if the value in Column A is 1A, Column B is 2A, Column C is 3A, Column D is 4A etc.. then my output result would be 1A2A3A4A."
...but not how you know you want A to D (rather than say A to G or just A and B)