exporting data in chunks

[dshafique]

Hi guys, Im trying ot figure out a way to split my data and export it to a csv, or another excel file. issue is, I have a huge dataset, I have it split into pieces by adding a black empty row above and below a "section". there's at least hundreds of sections.
a section is about 11 columns wide, and can be anywhere from 2 rows to 300+ rows.

a.) what would be better, to export them into different sheets, different spreadsheets, or a txt/csv file?
b.) how would I go about doing that?
c.) any other suggestions?

thanks!

 

[rpaulson]

try this. Make sure to seat a valid path in the myPath line;

Sub Creat_Txt_File()

myPath = "c:\results\"

fname = 1
Data = ""

For r = 2 To Range("A65536").End(xlUp).Row + 1

If Cells(r, "A") = "" Then 'create a file
myfile = myPath & fname & ".csv"
Open myfile For Append As #1 
Print #1 , Data
Close #1 
Data = ""
fname = fname + 1
Else

lcol = 11 ' last column
For c = 1 To lcol
delim = ""
If c < lcol Then delim = ","
Data = Data & Cells(r, c) & delim
Next c

End If
Data = Data & vbCr

Next r

MsgBox "Created " & fname - 1 & " files in " & myPath

End Sub

hth,
Ross

 

[dshafique]

thanks for the code, but when I tried it, it popped up saying that it created 0 files in path. I tried it with the path you provided, by creating a folder in my C. and I also tried it on my desktop, same thing. any idea why?

 

[rpaulson]

Do you have data in column A of your file?

Ross

 

[dshafique]

Column A is blank, my data is mainly from column 2. i first filter all the data, to get to where I am. i even tried with adding B instead of A, after doing so, i get a path/file error at

Open myfile For Append As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL]

these are my modifications of the code.

Sub Creat_Txt_File()

myPath = "c:\results"


fname = 1
Data = ""


For r = 2 To Range("B65536").End(xlUp).Row + 1


If Cells(r, "B") = "" Then 'create a file
myfile = myPath & fname & ".csv"
Open myfile For Append As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
Print [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] , Data
Close [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=1"]#1[/URL] 
Data = ""
fname = fname + 1
Else


lcol = 11 ' last column
For c = 1 To lcol
delim = ""
If c < lcol Then delim = ","
Data = Data & Cells(r, c) & delim
Next c


End If
Data = Data & vbCr


Next r
MsgBox "Created " & fname - 1 & " files in " & myPath


End Sub

thanks, Daoud

 

[rpaulson]

you need the "" after C:\results it should be c:\results\

also if data starts in column b then change For c = 1 To lcol to For c = 2 To lcol

hth,

Ross

 

[dshafique]

thank you so much, you've been a great help! its working now!