Save excel data in txt file using macro and in sequential series

[hjaspaul]

Hello Experts

Request you to help me build a macro to solve my current problem.

In excel sheet, I generally have cell entries of several thousands row in "Col 1".

I need to create macro where
a)It should automatically copy paste 1-1500 rows and paste them in Notepad and save notepad as Notepad 1.
b)Then it should copy paste values from 1501 to 3000 and paste them in Notepad and save notepad as Notepad 2.
c) Then it should copy paste values from 3001 to 4500 and paste them in Notepad and save notepad as Notepad 3.
d) and so on till the end of the Column1 values.

Data in column one in excel is something like below, and generally its of several thousands rows :-
http://subscriber.com/H/company360/o...23039000000000
http://subscriber.com/H/company360/o...nyId=463436001
http://subscriber.com/H/company360/o...nyId=468717210
http://subscriber.com/H/company360/o...nyId=468197934
http://subscriber.com/H/company360/o...nyId=463700194
http://subscriber.com/H/company360/o...nyId=564693430
http://subscriber.com/H/company360/o...nyId=564657070
http://subscriber.com/H/company360/o...nyId=469192392

 

[igold]

Hi,

You may want to check out this post which was easily found searching this site..

http://www.mrexcel.com/forum/excel-questions/863213-saving-notepad-visual-basic-applications.html


igold

 

[hjaspaul]

Thanks for the link, but code is not working correctly for me. Also that code is not solving my problem. I am new to VBA and dnt know much how to modify the code as per my requirement. It will be great if you can help me to solve my problem.

 

[hjaspaul]

I think following code with solve half of my problem. Can someone help to modify code to solve the complete issue. Current code actually copy and paste entire excel data into txt file and save file in date format. Whereas I want

a)It should automatically copy paste 1-1500 rows and paste them in Notepad and save notepad as Notepad 1.
b)Then it should copy paste values from 1501 to 3000 and paste them in Notepad and save notepad as Notepad 2.
c) Then it should copy paste values from 3001 to 4500 and paste them in Notepad and save notepad as Notepad 3.
d) and so on till the end of the Column1 values.


<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Sub saveText()
ActiveWorkbook.SaveAs filename:= _
ThisWorkBook.Path & "\textfile-" & Format(Now, "ddmmyy-hhmmss") & ".txt", FileFormat:=xlText, _
CreateBackup:=False
End Sub</code>

 

[Meggesto]

Sub SaveTXT()

Dim FileNumber As Integer
Dim RowCount As Long

RowCount = Range("A1048576").End(xlUp).Row
'Find Last Row in Column A

Files = Application.WorksheetFunction.RoundUp(RowCount / 1500, 0)
'Divides the number of items in Column A by 1500 to decide how many files / pages will be needed

Start = 1
Finish = 1500
'The beginning amounts for a loop that will increase by 1500

For FileNumber = 1 To Files Step 1
    ActiveWorkbook.Sheets.Add After:=ActiveSheet
    ActiveSheet.Name = FileNumber
        'Adds a new worksheet each pass of the loop to store data on
        
        For LoopRow = Start To Finish Step 1
            ActiveSheet.Cells(LoopRow, 1).Offset(-Start + 1, 0).Value = Sheet1.Cells(LoopRow, 1).Value
                Next LoopRow
                'This loops moves the data from 1 to 1500 to the newly created worksheet
                
            Start = Start + 1500
        Finish = Finish + 1500
        'This will increase start and finish values by 1500 after each loop to pull the required data.
        
     [COLOR="#FF0000"]Path = "C:\"[/COLOR]     
     ActiveWorkbook.SaveAs Filename:=Path & "Notepad" & FileNumber, FileFormat:=xlTextWindows
     'This will save the new worksheet as a TXT file (i.e labeled Notepad1) to the path of choice
     
Next FileNumber
'This will increment everything forward to the next set of filenumbers and start the process over
    
End Sub

Here is my approach. I assumed your data was in Sheet1 and from there created subsequent worksheets in the workbook that broke that data in chunks of 1500 lines starting in A1. After each chunk of data is moved to a worksheet it is saved as a TXT file with the label Notepad1, Notepad2 and etc depending on the amount of data and sheets created. You will need to fill in the path where you would like the files saved. I hope this helps.

 

[igold]

Hi Hjaspaul,

You specifically said twice (Post #s 1 & 4) that you wanted the file saved in Notepad. The link I provided which has responses from a Microsoft MVP - Excel, specifically said that you cannot use automation to save in Notepad. Hence my link to that post.

Although Meggesto's solution may work (I have not tested it), it is not saving in Notepad.

Just so you know...

igold

 

[hjaspaul]

Hi Meggesto/igold...

Code provided by meggesto instead of saving data in notepad saves in txt format....And it serves my purpose.

Apologise if my statement confused anyone. But issue is resolved and you guys are rockstar.....God bless you guys....Thanks a million(in infinite loop )