Export Macro to new Workbook based on Email Address

J

jhardie13

First off, completely brand new to all of this. I have been given the taskat work to break down a csv file that is a dump of multiple users address books, and sorts them out according to the email addresses. What I'm looking to do is create a macro that would be able to distinguish a change in email address, and make a new file when it notices the change. Example:

A B
[email protected] Data
[email protected] Data
[email protected] Data
[email protected] Data

Ideally, I would like to create a file with the [email protected] rows only in one file, followed by [email protected] in another file, using the same macro if possible. Not sure if this is even something that can be done, but Ifigured I'd take the shot considering the Excel file I have has over 300 address books and 6000 columns. Thanks in advance for any advice or suggestions!
 
J

jhardie13

First off, completely brand new to all of this. I have been given the task at work to break down a csv file that is a dump of multiple users address books, and sorts them out according to the email addresses. What I'm looking to do is create a macro that would be able to distinguish a change in email address, and make a new file when it notices the change. Example:



A B

[email protected] Data

[email protected] Data

[email protected] Data

[email protected] Data



Ideally, I would like to create a file with the [email protected] rows onlyin one file, followed by [email protected] in another file, using the same macro if possible. Not sure if this is even something that can be done, butI figured I'd take the shot considering the Excel file I have has over 300address books and 6000 columns. Thanks in advance for any advice or suggestions!
Click to expand...

One further thing I forgot to add, I would ideally like the name of the file being created be the name of email address from Column A.

Thanks again for anyone who even takes the time to read this.
 
G

GS

You need to clarify a few things...

1. Your example demos a change in email *server*, not email address.
This is not a problem but needs to be clarified. (email address
[email protected] is different than email address [email protected])

2. Your explanation suggests there are severall CSV files containing
data, AND that each CSV file is to be opened in Excel to be processed
into new workbooks. I do not recommend doing this as it will be orders
of magnitude faster and more efficient to just read the CSV files into
memory (via normal VB I/O functions) and process them there without
opening them in Excel.

3. <FWIW>
In terms of going forward with admin tasks, it doesn't seem very
efficient (to me, at least) to store the different email data in
separate files. Better (IMO) to put them all in one file on separate
sheets. This way, the file can be accessed as a database where each
sheet is a data table. This will allow you to access the data via
normal ADODB without having to open the file in Excel.

If you need separate files:
The data could be stored in separate CSV files (better than using
workbooks, IMO) for individual access based on server name. In this
case, each CSV file serves as an ADODB data table that you can access
without having to open each file. Alternatively, you can also read each
file into memory as previously mentioned.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
J

jhardie13

You need to clarify a few things...



1. Your example demos a change in email *server*, not email address.

This is not a problem but needs to be clarified. (email address

[email protected] is different than email address [email protected])



2. Your explanation suggests there are severall CSV files containing

data, AND that each CSV file is to be opened in Excel to be processed

into new workbooks. I do not recommend doing this as it will be orders

of magnitude faster and more efficient to just read the CSV files into

memory (via normal VB I/O functions) and process them there without

opening them in Excel.



3. <FWIW>

In terms of going forward with admin tasks, it doesn't seem very

efficient (to me, at least) to store the different email data in

separate files. Better (IMO) to put them all in one file on separate

sheets. This way, the file can be accessed as a database where each

sheet is a data table. This will allow you to access the data via

normal ADODB without having to open the file in Excel.



If you need separate files:

The data could be stored in separate CSV files (better than using

workbooks, IMO) for individual access based on server name. In this

case, each CSV file serves as an ADODB data table that you can access

without having to open each file. Alternatively, you can also read each

file into memory as previously mentioned.



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com
Click to expand...

I really appreciate the reply. If I wasn't so new to the Excel field, I would have been able to explain this more clearly the first time.

The file I have been given is in essence a dump file. We are switching ourmail server, and the file is a dump of all of our users address books. Originally, it contained several columns of information, which I have consolidated down to two colums, the email address, and the information that needsto be added in .abook form. An .abook is simply a glorified text file. The abook file is stored as [email protected] .

Now, my problem is, for each address book entry for a particular email address, it uses a new column. Example

[email protected] address 1
[email protected] address 2
[email protected] address 3
[email protected] address 1
[email protected] address 2
[email protected] address 1

I know I can simply copy and paste the address field and create a text filewith it, but the issue is the CSV file has over 6000 columns in it. This would be a lengthy procedure. Just was looking for an easier alternative than that.
 
G

GS

I really appreciate the reply. If I wasn't so new to the Excel
field, I would have been able to explain this more clearly the first
time.

The file I have been given is in essence a dump file. We are
switching our mail server, and the file is a dump of all of our users
address books. Originally, it contained several columns of
information, which I have consolidated down to two colums, the email
address, and the information that needs to be added in .abook form.
An .abook is simply a glorified text file. The abook file is stored
as [email protected] .

Now, my problem is, for each address book entry for a particular
email address, it uses a new column. Example

[email protected] address 1
[email protected] address 2
[email protected] address 3
[email protected] address 1
[email protected] address 2
[email protected] address 1

I know I can simply copy and paste the address field and create a
text file with it, but the issue is the CSV file has over 6000
columns in it. This would be a lengthy procedure. Just was looking
for an easier alternative than that.
Click to expand...

Okay.., that helps some. So then the addresses are not email addresses?

I've never heard of an address book 'dump' (or any other kind of data
dump) that stacks data to the right. Normally it would be appended
below existing data. (I doubt your source address books have 6000 data
fields!)

What's needed is to know how the data is laid out in the dump file, and
what data gets parsed into your .abook file. The fact that the .abook
file is a text file is a bonus that makes the task all that much
simpler to perform. Can you provide a sample of the source data dump
file?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
J

jhardie13

Okay.., that helps some. So then the addresses are not email addresses?



I've never heard of an address book 'dump' (or any other kind of data

dump) that stacks data to the right. Normally it would be appended

below existing data. (I doubt your source address books have 6000 data

fields!)



What's needed is to know how the data is laid out in the dump file, and

what data gets parsed into your .abook file. The fact that the .abook

file is a text file is a bonus that makes the task all that much

simpler to perform. Can you provide a sample of the source data dump

file?



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion







---

This email is free from viruses and malware because avast! Antivirus protection is active.

http://www.avast.com
Click to expand...


The reason the second row is only data is because I've already consolidated it to the format that the .abook requires in the file. A sample line looks like this.

A B
[email protected] Rose|Rose|Last Name|[email protected]|
[email protected] John|John|Doe|[email protected]|

Each address book entry is an individual column in the workbook.

Really appreciate your advice in this matter
 
J

jhardie13

The reason the second row is only data is because I've already consolidated it to the format that the .abook requires in the file. A sample line looks like this.



A B

[email protected] Rose|Rose|Last Name|[email protected]|

[email protected] John|John|Doe|[email protected]|



Each address book entry is an individual column in the workbook.



Really appreciate your advice in this matter
Click to expand...

Someone gave me this code, but it doesn't seem to be exactly what I need, and I'm too dumb to be able to fine tune it :)


Public Sub Transfer()
Dim C_ell As Range, P_ath As String
'
'Text files will be saved in the same folder as this macro file.
P_ath = ActiveWorkbook.Path
'
'Sheet1 can be changed to your main data sheet
'Same thing for Sheet2, use the you want but change it in all places in the code
'
Sheets("Sheet1").Select
Range("A1").Select
Range("A1").EntireRow.Copy
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
For Each C_ell In Range("A2", Cells(Rows.Count, 1).End(xlUp))
If C_ell = C_ell.Offset(-1, 0) Then
C_ell.EntireRow.Copy
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
Else
Sheets("Sheet2").Select
Range("A1").EntireRow.Delete
Sheets("Sheet2").Copy
ActiveWorkbook.SaveAs Filename:= _
P_ath & "\" & Range("A1") & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close False
ActiveSheet.Cells.ClearContents
Sheets("Sheet1").Select
End If
Next
'this section saves the last email in a text file
Sheets("Sheet2").Select
Range("A1").EntireRow.Delete
Sheets("Sheet2").Copy
ActiveWorkbook.SaveAs Filename:= _
P_ath & "\" & Range("A1") & ".txt", _
FileFormat:=xlText, CreateBackup:=False
ActiveWindow.Close False
ActiveSheet.Cells.ClearContents
Sheets("Sheet1").Select

End Sub
 
G

GS

I see that! Also doesn't do what I suggest!

I still would like to see a sample of the CSV dump file along with a
sample output file<g>. Can you upload to a public webshare and post a
link so I can download and work with 'real' data?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Top