Looking to split a large excel file by rows and keep the header

[flyguy]

I am trying to split up an excel file that has multiple lines, I want to break this into groups of 500 lines and retain the original header info for each file. This file will always have the same header just different amount of lines each time. Is this possible to do with a macro/vba. I have no real experience with macros for programming. It would be great if it could auto save as file1-file2 etc. file name does not matter. Just breaking apart manually is time consuming and looking to see if it can be automated.
The file starts as a .csv and needs to be converted to an .xls

Thank you for your assistance.

 

[MoshiM]

did that and get compile error and Sub flyguy() is yellow
Sub flyguy()




Dim ACS As Range, Z As Long, New_WB As Workbook, B As Long, _

Any error messages about an ambiguous name?

 

[flyguy]

Any error messages about an ambiguous name?

There was not. I hate to take up any more of your time and am grateful for what you have supplied.
I can work with the original code that you supplied.
Have a safe and Happy New Year.

 

[flyguy]

Getting an error and the below text is red


Dim ACS As Range, Z As Long, New_WB As Workbook, B As Long, _

I am able to work with the original code. I appreciate your time and help.

 

[brynesir87]

Make the worksheet with the data the active sheet and run this. It should save to the same path as the csv.

Sub flyguy()

Dim ACS As Range, Z As Long, New_WB As Workbook, _
Total_Columns As Long, Start_Row As Long, Stop_Row As Long, Copied_Range As Range

Dim Headers() As Variant

Set ACS = ActiveSheet.UsedRange

With ACS

    Headers = .Rows(1).Value
    Total_Columns = .Columns.Count
   
End With

Start_Row = 2

Do While Stop_Row <= ACS.Rows.Count
   
    Z = Z + 1
   
    If Z > 1 Then Start_Row = Stop_Row + 1
   
    Stop_Row = Start_Row + 499
   
    With ACS.Rows
        If Stop_Row > .Count Then Stop_Row = .Count
    End With
   
    With ACS
        Set Copied_Range = .Range(.Cells(Start_Row, 1), .Cells(Stop_Row, Total_Columns))
    End With
   
    Set New_WB = Workbooks.Add
   
    With New_WB
   
        With .Worksheets(1)
            .Cells(1, 1).Resize(1, Total_Columns) = Headers
            .Cells(2, 1).Resize(Copied_Range.Rows.Count, Total_Columns) = Copied_Range.Value
        End With
       
       .SaveAs ACS.Parent.Parent.Path & Application.PathSeparator & "file-" & Z & ".xls", FileFormat:=-4143
       .Close
      
    End With
   
    If Stop_Row = ACS.Rows.Count Then Exit Do
   
Loop

End Sub

This code works in what I want too, bye the way, if I would like to save file in .csv (xlCSVUTF8) File Format = 62 if refer to this link (XlFileFormat enumeration (Excel))
But it shown error as image attached, may I know how to solve it?

 

[MoshiM]

This code works in what I want too, bye the way, if I would like to save file in .csv (xlCSVUTF8) File Format = 62 if refer to this link (XlFileFormat enumeration (Excel))
But it shown error as image attached, may I know how to solve it?

Change ".xls" in the .saveas line to ".csv"

 

[brynesir87]

Change ".xls" in the .saveas line to ".csv"

I do have change to ".csv" and remain the "FileFormat:=-4143", it is ok to save in CSV file but a normal CSV file.
Then I also change to ".csv" and change the FileFormat to "FileFormat:=62", it shown error as below

The way can work for me now is like that:
first step : use the original code given and just did a minor change from ".xls" to ".csv"
second step : after done the VBA above, I change the code from the "FileFormat:=-4143" to "xlCSVUTF8", then it becomes the file format which I want.

Anyway to merge the 2-steps above into one for saving time?

*my main purpose is to save the my file into .csv (xlCSVUTF8 format) as photo below, not the normal CSV file

 

[MoshiM]

I do have change to ".csv" and remain the "FileFormat:=-4143", it is ok to save in CSV file but a normal CSV file.
Then I also change to ".csv" and change the FileFormat to "FileFormat:=62", it shown error as below
View attachment 54847

The way can work for me now is like that:
first step : use the original code given and just did a minor change from ".xls" to ".csv"
second step : after done the VBA above, I change the code from the "FileFormat:=-4143" to "xlCSVUTF8", then it becomes the file format which I want.

Anyway to merge the 2-steps above into one for saving time?

*my main purpose is to save the my file into .csv (xlCSVUTF8 format) as photo below, not the normal CSV file
View attachment 54848

I do have change to ".csv" and remain the "FileFormat:=-4143", it is ok to save in CSV file but a normal CSV file.
Then I also change to ".csv" and change the FileFormat to "FileFormat:=62", it shown error as below
View attachment 54847

The way can work for me now is like that:
first step : use the original code given and just did a minor change from ".xls" to ".csv"
second step : after done the VBA above, I change the code from the "FileFormat:=-4143" to "xlCSVUTF8", then it becomes the file format which I want.

Anyway to merge the 2-steps above into one for saving time?

*my main purpose is to save the my file into .csv (xlCSVUTF8 format) as photo below, not the normal CSV file
View attachment 54848

So you want this?

       .SaveAs ACS.Parent.Parent.Path & Application.PathSeparator & "file-" & Z & ".csv", FileFormat:=xlCSVUTF8

 

[brynesir87]

So you want this?

       .SaveAs ACS.Parent.Parent.Path & Application.PathSeparator & "file-" & Z & ".csv", FileFormat:=xlCSVUTF8

Yes, theoretically yes, both ".csv", & "FileFormat:=xlCSVUTF8" are what I want.
I did use this coding just now, but failed too, same error code

I am using excel version as below:

 

[MoshiM]

Yes, theoretically yes, both ".csv", & "FileFormat:=xlCSVUTF8" are what I want.
I did use this coding just now, but failed too, same error code
View attachment 54850
View attachment 54851

I am using excel version as below:
View attachment 54852

It looks like only a partially automatic approach is viable on MACs.

https://answers.microsoft.com/en-us/msoffice/forum/all/vba-macro-in-excel-2016-for-mac-saveas-will-not/86d6855f-241d-4b8d-b233-cccd7184c67b

I don't have access to a MAC to test on but based on the link above I'd suggest running the following code as is and if it doesn't work then un-comment the application.getsaveasfilename line (In this case you may have to manually select the file type)

Sub flyguy()

Dim ACS As Range, Z As Long, New_WB As Workbook, _
Total_Columns As Long, Start_Row As Long, Stop_Row As Long, Copied_Range As Range, file_name As String, file_access As Variant

Dim Headers() As Variant

Set ACS = ActiveSheet.UsedRange

With ACS
    Headers = .Rows(1).value
    Total_Columns = .Columns.Count
End With

Start_Row = 2

file_access = GrantAccessToMultipleFiles(Array(ThisWorkbook.Path))

Do While Stop_Row <= ACS.Rows.Count
  
    Z = Z + 1
  
    If Z > 1 Then Start_Row = Stop_Row + 1
  
    Stop_Row = Start_Row + 499
  
    With ACS.Rows
        If Stop_Row > .Count Then Stop_Row = .Count
    End With
  
    With ACS
        Set Copied_Range = .Range(.Cells(Start_Row, 1), .Cells(Stop_Row, Total_Columns))
    End With
  
    Set New_WB = Workbooks.Add
  
    With New_WB
  
        With .Worksheets(1)
            .Cells(1, 1).Resize(1, Total_Columns) = Headers
            .Cells(2, 1).Resize(Copied_Range.Rows.Count, Total_Columns) = Copied_Range.value
        End With
      
       file_name = ACS.Parent.Parent.Path & Application.PathSeparator & "file-" & Z & ".csv"
      
       'file_name = Application.GetSaveAsFilename(file_name)
      
       .SaveAs file_name, FileFormat:=xlCSVUTF8
       .Close
     
    End With
  
    If Stop_Row = ACS.Rows.Count Then Exit Do
  
Loop

End Sub

 

[Kevinwithluv]

Hello all - this code works like charm. But i do have following requirement and some challenges.

1) am planning to split thr files by 1 lakh records. I tried testing this formula for 7 lakh records and it's working like charm. But the issue is, its not working for a case when the rows are less than 1 lakh records. What happens is it splits the first file with available total records but then the file split still continues with header only. I am not sure where I went wrong. What i want is if the file records are less than 1 lakh, then the file split should be just one file.
2) I would want a button and provide user to select the file to split. Once the user selects the file, then the splitting of files by 1 lakh records happens

Can some please help..

I tried many places but not able to reach anywhere...

Please help