Separate string and place into specific cells

[genestoy]

Hi,
My first post here and I need some help and am hoping this is not too hard to do, but I am lost on how to do this.
I have a worksheet with 13 columns and many rows. The columns contain numbers, people names, city names, state abbreviations and email addresses, etc. I have an online form that the user enters this information into and the form then sends the form to me. I can configure the form to send the info any way I wish and I have been inserting a new row in the worksheet and then manually filling in each of the 13 cells. I would like to be able to just do a copy and paste of the complete row all at once. Sometimes one or two of the cells will be blank such as a people name with no spouse and that cell will need to remain blank. What code do I need to use to separate the info to go into the 13 cells?
Example:
Member # (code?) Name (code?) Spouse Name (code?) City (code) State (code?) etc, etc.

I can configure the form to send me the complete line to paste including automatically inserting the user data into the code line so then all I would have to do is paste it into the worksheet manually as one row.
Hope that explains what I want to do and appreciate any help I can get with this. I am using Excel 2002
Thanks, Gene

[Leith Ross]

Hello genestoy,

Welcome to the Forum!

It would be best if you posted the workbook. When writing macros it is best to see how the data is laid out, what formulas are used, what type of formatting there is etc., as these impact the code development. If you have any material that is confidential, please make a copy of the workbook without this information or change it prior to posting.

[genestoy]

How do I post the worksheet as the forum won't allow the upload because of its size 1.43 mb?

I saved it as a xlsx file and hope that works as it was smaller

[Leith Ross]

Hello genestoy,

Use WinZip or some similar program to compress the file. The file extension must be .zip to upload to the forum.

[genestoy]

I edited the post above and saved it as an xlsx file, hope that is what you needed?

[Leith Ross]

Hello genestoy,

That works too. When you upload a file, the dialog lists the file sizes by extension. Then you will know if have to zip the file or not.

[Leith Ross]

Hello genestoy,

Could you post some sample strings that I can use?

[genestoy]

Not quite sure what you mean but here is what goes in the cells

A1 - John Doe - Jane Doe - Phoenix - AZ - Bayliner - 20 - 2009 - 6 - 15 - 19 - [email protected] - Baby Doe
A6 - Bob Smith - Carla Smith - Los Angeles - CA - Hatteras - 40 - 2005 - 3 - 15 - 20 - [email protected] - (blank)

[genestoy]

I think I need a macro that invokes the Import Wizard and just sets the delimiter to the semicolon as that is what I have set up for the data for each cell. All the defaults are fine for the import wizard except for the semicolon and the space unchecked. If someone could do that for me I would be grateful, I am newbie to this macro stuff so please tell me each step I need to do to set it up! I have tried the string using the semicolon as a separator, clicking on the first cell, doing a ctrl v, then clicking on the icon below the text using the Import Wizard and it works just fine. Would also be nice if the macro inserted a new row and did the above at the same time!
Thanks, Gene

Please Login or Register  to view this content.

[genestoy]

anyone know how?

[Leith Ross]

Hello genestoy,

Can you post the text file you import?

[genestoy]

A19; Bill Smith; Mary Smith; Phoenix; AZ; Bayliner; 21; 2003; 8 ; 15; 19; [email protected] ; Joey

I did above but guess you missed it

[Leith Ross]

Hello genestoys,

That's a text file??? I need more examples than you have provided to correctly code a macro for this. If you don't have or don't want to post the file then I would gladly accept 10 lines like this to work from.

[genestoy]

That is exactly what I put in the cells only the info changes of course for each entry. I don't understand what else you need?

[Leith Ross]

Hello genestoy,

I will write a macro based on this single line. However, in text files there are various ways of formatting the lines. So without more examples I can't be sure it will correctly copy the data.

Will the number of fields (columns) always be the same number?

If there is missing data will there be just a blank field or fewer fields?

Are all fields separated by a semicolon and space even at the end of the line?

[genestoy]

Thanks for trying to help!

I should explain better-- I simply copy that line to the clipboard from my form and paste it into the excel sheet, at least that is what I am trying to streamline.
Yes, the same amount of columns always (13)
if there is missing data then that cell will remain blank
All fields are separated by a semicolon but none at the end

I can change the semicolons and or spaces if that makes it easier for you

Hope that helps

[genestoy]

I edited the message above

[Leith Ross]

Hello genestoy,

Sorry to hound you with all these questions, but I need to know. Do you want the text to be pasted to the location of the first field, i.e., A!, A19, etc. or just to the next empty row?

Do you want to run the macro using a button or key combination?

[genestoy]

Yes, the first field would be A of the new row that would be inserted (which could be anywhere in the list of rows)
I would really like a button if possible

[genestoy]

I edited the message above again, and don't be sorry, you are the one helping me!

[Leith Ross]

Hello genestoy,

Okay, I found the files I needed. This macro will set the "Insert" key to paste the information into the next available row on the worksheet. Just using CTRL+C to copy the data and then press "INSERT" and your done! Everything here has been added to the attached workbook.

Here is the macro code for those that are interested...

Workbook_Open Event Code

Please Login or Register  to view this content.

Module1 Macros Code

Please Login or Register  to view this content.

Clipboard API Code

Please Login or Register  to view this content.

[genestoy]

Thanks so much! There are a couple of small glitches that i need fixed for it to work for me though,
1--I need it to insert the info on the row that I want it on, it inserts it now at the last blank row. i insert a new row and I need it to fill in that row where ever that row might be

2--The column I is not centering the number like all the rest do?

[Leith Ross]

Hello genestoy,

That is easy to correct. I glad inserting from the clipboard worked without problems. I fix up the code.

[Leith Ross]

Hello genestoy,

Here is the revised macro and workbook. I only had to chenge the macro below.

Please Login or Register  to view this content.

[genestoy]

Leith,
The new version does not work for me at all now?

[Leith Ross]

Hello genestoy,

There may be a problem in converting between 2003 back to 2007. Copy the code from the 2003 workbook into the 2007 workbook. Save the 2007 workbook as an xlsm. Close it and then reopen the workbook. It should work.

[genestoy]

Leith,
I have Excel 2002, so does what you are saying apply?
Thanks, Gene

[Leith Ross]

Hello Gene,

The file I opened was xlsx extension which is Excel 2007 and up. My computer converted it to Excel 2003 format. The macro code should run on either 2002 or 2003 machine. Delete the workbook you downloaded and try downloading another copy the file from the forum. If that fails, I will put together an Excel 2000 to 2003 file with the macro and post it for you.

[genestoy]

Leith,
I re-downloaded the file and still no joy. If you could redo it in the 2002 format I would appreciate it.
Thanks, Gene

[Leith Ross]

Hello genestoy,

Here is the rebuilt workbook. This works in Excel 2000 and 2003. Shouldn't be any reason for not to work in Excel 2002.

[genestoy]

This is too weird, this one is not working either and is not the same format as the one I sent you? I think I will just try to live with the first version for now, and I really appreciate your help. I was able to change the form to send the "I" column differently so it is now centering correctly.
Gene

[Leith Ross]

Hello genestoy,

Weird doesn't begin to describe this! The code in the first workbook is basically the same as the code in the last workbook. Maybe you should have your computer exorcised. I am stumped.

[Leith Ross]

Hello genestoy,

The problems are most likely due to your version of Excel 2002 not having Service Pack 3 installed. Here are the instructions on downloading How to download and install the update.

I just helped another poster SarahPintal, who is having simple code problems involving Excel 2002. Her problem's are almost 100% identical to the issues you and I experienced.

To Check the service pack version you have, do the following:

1. Press the Window key (key with the Windows logo on it) and the Pause/Break key together.
2. This will display the System Properties dialog.
3. Select the General tab.

[genestoy]

Hi Leith,
Nope, I ave SP3 installed so not the problem.
Gene

[genestoy]

Hi Leith,
I am home now (was traveling when I started this post) and was wondering if you could upload a copy for Excel 2007 which I will try on a friends machine with Excel 2007 and see if it will work, if so i will just upgrade to 2007.
Thanks, Gene

[genestoy]

Can somebody please help me with the first zip file that Leith posted above (Auto-Insert Text ver 1.zip)? It does insert the rows as I need it to but when trying to insert another row it always changes the row above it and I need it to insert the new information not change the row above its information. I really need some help with this and have been trying to figure it out for some time. Is Leith no longer here anymore?
Thanks, Gene

I am sure it has to do with this insert code why it won't insert on the highlighted line?

Please Login or Register  to view this content.

Never mind, I got it fixed it thanks to another forum

Please Login or Register  to view this content.