Help

[royal99]

Hello Experts,

I am looking for a help to create a macro where it will only copy 7000 rows at a time then open a fresh notepad file and paste then save it in number as filename for notepad.This worksheet may have lacs of rows . so it will copy and paste bunch of 7000 records only in a notepad and then go back to excel copy another bunch of rows and again open a new notepad file, paste the copied record this will go on till the last row, the last bunch may left with less then 7000 rows, that's fine.

Please note
The worksheets has data in first 3 columns then 2 columns are blank and sixth column has a number
The final output of a row in notepad
Will be as e.g.
Column1,Column 2,Column 3,,,11


Could you please help me on this.

[Kenneth Hobs]

Welcome to the forum!

Please use a more explicit title when seeking help in forums like this for future posts.

"Save it in number as filename" is unclear. Can you give a few example filenames?

Will the target folder be the same as the source folder?

Normally, a tab would be the column delimiter when doing such. We can transform that to a comma but doing so comes at a risk.

[royal99]

hi Kenneth,

I am sorry for that,

Regarding saving, I mean to save file, for e.g. 1.txt, 2.txt, 3.txt, so on.
Yes target folder will be same as source

But is this possible?
Thanks

[Kenneth Hobs]

Sure. There are some assumptions I had to make but this should get you close.

I recommend making a short test file and test folder and put it there. Be sure to set the reference as I commented for the clipboard function. Change the value of nSet from 10 to your 7000 in your production version.

Sub RowSetsToTextFiles()  Dim rng As Range, r As Range, lRow As Long
  Dim sSeparator As String
  Dim sEncoding As String
  Dim ado As Object
  Dim bRow As Integer, sRow As Long, nSet As Integer
  Dim nCols As Integer, lv As Long
  Dim tFolder As String, s As String
  
  '-------------------------------------------------------------------------------------
  'CHANGE THESE PARAMETERS TO SUIT
  Set rng = ActiveSheet.UsedRange 'this is the range which will be written to text files less the first row.
  sSeparator = "," 'vbTab 'e.g. for pipe seperated value, change this to "|"
  sEncoding = "ASCII" 'e.g., ASCII, "UTF-8"
  bRow = 2  'beginning row
  nSet = 10 'number of rows per set
  tFolder = ThisWorkbook.Path & "\" 'Target folder to write text files to.
  '-------------------------------------------------------------------------------------
   
  nCols = rng.Columns.Count
  Set ado = CreateObject("ADODB.Stream")
  For lv = nSet To WorksheetFunction.Floor(rng.Rows.Count / nSet, 1) * nSet Step nSet
    With ado
      .Type = 2
      .Charset = sEncoding
      .Open
      Set r = Range(Cells(lv - nSet + bRow, 1), Cells(lv + bRow - 1, nCols))
      r.Copy
      s = Replace(getClipboard(), vbTab, sSeparator)
      .WriteText s
      .SaveToFile tFolder & lv / nSet & ".txt", 2
      .Close
    End With
  Next lv
  
  ' Write the last remaining rows for a partial set to the last text file.
  With ado
    .Type = 2
    .Charset = sEncoding
    .Open
    Set r = Range(Cells(lv - nSet + bRow, 1), Cells(rng.Rows.Count, nCols))
    r.Copy
    s = Replace(getClipboard(), vbTab, sSeparator)
    .WriteText s
    .SaveToFile tFolder & lv / nSet & ".txt", 2
    .Close
  End With
  Set ado = Nothing
End Sub


Function getClipboard()
'Add Reference:   'Reference: Microsoft Forms 2.0 Object Library
    Dim MyData As DataObject
     
    On Error Resume Next
    Set MyData = New DataObject
    MyData.GetFromClipboard
    getClipboard = MyData.GetText
End Function

[p45cal]

edit post posting: Kenneth beat me to it..

try running this macro, it might give you what you want:

Sub blah()
On Error GoTo XIT
Application.ScreenUpdating = False
NoOfRowsPerFile = 7000 'adjust to how many rows you want in each file
Set SceSht = ActiveSheet
LastRow = SceSht.Cells(Rows.Count, "A").End(xlUp).Row
FileCount = 1
For i = 1 To LastRow Step NoOfRowsPerFile
  Set SceRng = SceSht.Cells(i, 1).Resize(NoOfRowsPerFile, 6)
  Set NewSht = Sheets.Add
  NewSht.Range("A1").Resize(NoOfRowsPerFile, 6).Value = SceRng.Value
  NewSht.Move
  With ActiveWorkbook
    Application.DisplayAlerts = False 'move this line to after the next line if you want to be alerted about overwriting an existing file.
    .SaveAs Filename:=ThisWorkbook.Path & "\" & Format(FileCount, "000") & ".txt", FileFormat:=xlCSV, CreateBackup:=False
    FileCount = FileCount + 1
    .Close
    Application.DisplayAlerts = True
  End With
Next i
XIT:
Application.ScreenUpdating = True
End Sub

[royal99]

This is exactly what I was looking for.....You are awesome....!

Thanks a lot for taking time and working on my query.....! Love You, ,

Hey Kenneth, I want to master VBA like you do....I am very very serious, Please suggest, Please Guide,

Awaiting Reply
royal99

[royal99]

Hey

Thank a lot for taking time and helping me. its working as per my requirement, You people are Terrific. In output file, I mean .txt file I get numbers like this on left hand side (1, 2, 1, 4), its not required
1.Column 1,Column2,Column3,,,45
2.Column 1,Column2,Column3,,,45
1.Column 1,Column2,Column3,,,45
4.Column 1,Column2,Column3,,,45

and the column1 (which has some 15 digit number) that number gets changed into 4601E+14 in .txt file instead of a 15 digit number.

Thanks again

[Kenneth Hobs]

The best way to get help is to help us help you. Do that by posting a short example file. Click the Go Advanced button in lower right of a response, and click the paperclip icon to browse to the file to upload it. Obviously, you should obfuscate sensitive data.

If you need to trim some data from the left, one would put the string into a string array and use InStr() to find the "." and use Right() with Len(). Another approach besides the .Copy method would be needed to handle the large numbers issue.

Be sure to open the TXT files in NotePad rather than Excel to view the true exported data.

Trying to help others in forums like this is how I improve my skills such as they are. This forum has an Articles section and a kb, Knowledge Base, that has some good examples. Many have Training courses you can pay and learn more. The are lots of books and cdroms one can buy as well.

Of course there are many tutorials that are free or not. e.g. http://lmgtfy.com/?q=Excel+VBA+Tutorials

[royal99]

Hey P45cal

Thank a lot for taking time and helping me. its working as per my requirement, You people are Terrific. In output file, I mean .txt file I get numbers like this on left hand side (1, 2, 1, 4), its not required
1.Column 1,Column2,Column3,,,45
2.Column 1,Column2,Column3,,,45
1.Column 1,Column2,Column3,,,45
4.Column 1,Column2,Column3,,,45

and the column1 (which has some 15 digit number) that number gets changed into 4601E+14 in .txt file instead of a 15 digit number.

Thanks again

[p45cal]

The best way to get help is to help us help you. Do that by posting a short example file. Click the Go Advanced button in lower right of a response, and click the paperclip icon to browse to the file to upload it. Obviously, you should obfuscate sensitive data.

…but you've read that somewhere else.

[royal99]

To P45Cal,

I think you missed by reply as I mentioned above, that your code runs fine but only a small issue is its shows numbers like this on left hand side (1, 2, 1, 4), its not required
1.Column 1,Column2,Column3,,,45
2.Column 1,Column2,Column3,,,45
1.Column 1,Column2,Column3,,,45
4.Column 1,Column2,Column3,,,45

and the column1 (which has some 15 digit number) that number gets changed into 4601E+14 in .txt file instead of a 15 digit number in notepad.

Thanks in advance

[p45cal]

To P45Cal,

I think you missed by reply

No, I saw it. It didn't help at all.