separate data with semi colons

[**dan**]

hi
i have an excel doc with 1800 email addresses and i need to transfer the data into a file where the addresses are separated not in rows but with semi colons between the addresses

 

[GlennUK]

Err, so you are saying you want 1800 email addresses in one row, separated by semi-colons? Really?!?!?

 

[**dan**]

yes, i need to paste them in outlook so they need to be separated by semi colons

 

[GlennUK]

I would think that you'd need to put this in a string in the first cell, but there is the limit of 32,767 characters max as the contents of the cell. I don't suppose you know the total number of characters of the list ( + 1799 extra for the semi-colons )?

 

[**dan**]

it actually dont need to have the result in excel. It would be fine to have the addresses separated with semi colons in a text file.

 

[GlennUK]

Writing a macro to stuff them all into one row of an output file still has the limit of 32,767 characters ...

Help says:

Open pathname For mode [Access access] [lock] As [#]filenumber [Len=reclength]

The Open statement syntax has these parts:

Part Description
pathname Required. String expression that specifies a file name¾may include directory or folder, and drive.
mode Required. Keyword specifying the file mode: Append, Binary, Input, Output, or Random. If unspecified, the file is opened for Random access.
access Optional. Keyword specifying the operations permitted on the open file: Read, Write, or Read Write.
lock Optional. Keyword specifying the operations permitted on the open file by other processes: Shared, Lock Read, Lock Write, and Lock Read Write.
filenumber Required. A valid file number in the range 1 to 511, inclusive. Use the FreeFile function to obtain the next available file number.
reclength Optional. Number less than or equal to 32,767 (bytes). For files opened for random access, this value is the record length. For sequential files, this value is the number of characters buffered.

 

[hsk]

List all the email address from A1 to A1800

then Put cell B1 = A1
And cell B2 =concatenate(B1,";",A2)

Copy the cell B2 till 1800 row,
B1800 will give all the email addresses in the form u want