VBA to split xlsx into multiple CSV files based in format

[diwio]

Hi all,

I'm struggling with this, I need to split this document so I get a CSV for each unique site formatted as per below.. Users will be in multiple sites

Current

Export Data	Username	Site Name	Current Perm	New Group
site_exampleSite_SiteManager	exampleUser	exampleSite	siteManager	GG_exampleSite_exampleUser_Modify
site_exampleSite_SiteRead	exampleUser1	exampleSiteBlah	siteRead	GG_exampleSiteBlah_exampleUser_Read
site_exampleSite_SiteContribute	exampleUser2	exampleSite	siteContribute	GG_exampleSite_exampleUser2_Modify
site_exampleSiteBlah_SiteManager	exampleUserBlah	exampleSiteBlah	siteContribute	GG_exampleSiteBlah_exampleUserBlah_Modify
site_exampleSiteBlah_SiteRead	exampleUserBlah1	exampleSiteBlah	siteRead	GG_exampleSiteBlah_exampleUserBlah1_Read
site_exampleSiteBlah_SiteContribute	exampleUserBlah2	exampleSite	siteContribute	GG_exampleSite_exampleUser2Blah_Modify

Desired
CSV file for each unique siteName formatted as per:

Group (contains New Group data)	User-Name (Contains Username data)
GG_exampleSite_exampleUser_Modify	exampleUser
GG_exampleSiteBlah_exampleUser_Read	exampleUser1

Edit, the end goal is to do an AD Import to add users to their respective groups based on the sitename and permission level required.

 

[rpaulson]

try this,

Sub output_to_textfile()
mypath = "C:\test\"

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    data = Cells(r, "E") & "," & Cells(r, "B")
    Open mypath & Cells(r, "C") & ".csv" For Append As #1
    Print #1, data
    Close #1

Next r
 
MsgBox "Done"

End Sub

 

[diwio]

try this,

Sub output_to_textfile()
mypath = "C:\test\"

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    data = Cells(r, "E") & "," & Cells(r, "B")
    Open mypath & Cells(r, "C") & ".csv" For Append As #1
    Print #1, data
    Close #1

Next r
 
MsgBox "Done"

End Sub

That looks to work perfectly thanks!

On second thoughts, it'd probably be better to have them generated as separate csv's like they are currently, and then also combined into one big one, would make AD import MUCH easier? Any chance of adding that functionality - Much appreciated!

 

[rpaulson]

Sub output_to_textfile()
mypath = "C:\test\"
all_text=""

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
data = Cells(r, "E") & "," & Cells(r, "B")
Open mypath & Cells(r, "C") & ".csv" For Append As #1
Print #1, data
Close #1
all_text=alltext & data & vbcr & vblf
Next r

Open mypath & "all.csv" For Append As #1
Print #1, all_text
Close #1


MsgBox "Done"

End Sub

 

[diwio]

Sub output_to_textfile()
mypath = "C:\test\"
all_text=""

For r = 2 To Cells(Rows.Count, "A").End(xlUp).Row
data = Cells(r, "E") & "," & Cells(r, "B")
Open mypath & Cells(r, "C") & ".csv" For Append As #1
Print #1, data
Close #1
all_text=alltext & data & vbcr & vblf
Next r

Open mypath & "all.csv" For Append As #1
Print #1, all_text
Close #1


MsgBox "Done"

End Sub

In the all.csv output it doesn't seem to insert the data correctly.

Left side of screenshot is the all.csv vs the right side which shows the individual sheets.