Exporting query results to Excel!!! 1

[FuzzyBear9]

Hi,

I want to export data (using the 'analyze this' feature)from a query into Excel. The data is needed for mass e-mailer program under Lotus Notes R.5, but have encountered two problems:

1) When I export the hyperlink field to Excel, its properties are retained. The hyperlink field contains e-mail addresses starting with 'mailto:'. How can I modify my query to capture just the text?

Example

CHANGE: mailto:test@example.com (as hyperlink)
To: test@example.com (as text)

2) When I export the data to Excel, the field names are retained (obviously), but the mass e-mailer program requires different names from the ones used in my tables.

Example

Query results (field names): Email Client Firstname(s)
Mass e-mailer: SendTo Company FirstName

Is there an easy way to rename the fields on import to Excel? What I want to end up with is a command button on a form that runs the query and saves the data as an Excel file in the required format!

Any ideas? Thanks for listening.

 

[FuzzyBear9]

OK!! I have managed to resolve the second problem with renaming fields. It was simple enough!! Just had to reference each field in the query by its new name.

Eg. SendTo: Email (in the field row in query design).

Still stick with the first problem though!!!

Any help would be greatly appreciated!!

 

[RegionsRob]

To get just the email address without the "MAILTO:", I would create a calculated field in the query. Assuming that MAILTO: is at the beginning of each entry in the field in your table, you can use the MID function to start at the 8th character in the field and return the rest of the string. (I also like using TRIM to delete any possible leading spaces.)

ShortEmail: MID(TRIM(email),8)

 

[FuzzyBear9]

Thank you RegionsRob.

I modified the example you gave and it worked perfectly, but for one small item! Each e-mail address has the '#' symbol at the end of it. I have tried to modify the calculated field but to no avail. I think I need to use LEN, but I am not sure.

Can you help?

 

[RegionsRob]

I think you can get it with LEN. Using the code I gave you before, try:

ShortEmail: MID(TRIM(),8, LEN([email])-8)

The 8 is the number of the character in the string where you want to start (skipping the 7 characters of MailTo: ). The second parameter is the number of characters you want to return. You don't want to include the 7 characters you are skipping, and you don't want to include the last character, therefore LEN([email])-8

Hope this helps,
Rob
PS - I like your handle.

 

[FuzzyBear9]

RegionsRob, you are a star! It worked!

Whatever happened to the Muppets?