software

Hi All,

I have a problem formatting an existing Excel spreadsheet into a format
acceptable by a database which needs each NAME cell to be separated by
5 blank rows.

I have the following fields...

NAME, ID, ADDRESS, TELEPHONE/FAX/EMAIL, CONTACT, CODES

(where there can be 1-8 rows of CODES for each NAME)

Can anyone think of a way to automatically delete/insert rows (ignoring
that there may be info in CODES), so that NAME is every 6 rows??

eg

Bob 5 1 / 2 Rodeo Drive 5551245 Jane QWE
Springfield ASD
WA ZXC
76767 QAZ
LKJ
JHG
OIU
HUY
Bob2 6 1 / 2 Rodeo Drive 5551245 Jane QWE
Springfield
WA
76767You need vba code to do it. How does the data be inserted anyway?

www.geocities.com/excelmarkswayquot;Mr_Flibblequot; wrote:

gt; Hi All,
gt;
gt; I have a problem formatting an existing Excel spreadsheet into a format
gt; acceptable by a database which needs each NAME cell to be separated by
gt; 5 blank rows.
gt;
gt; I have the following fields...
gt;
gt; NAME, ID, ADDRESS, TELEPHONE/FAX/EMAIL, CONTACT, CODES
gt;
gt; (where there can be 1-8 rows of CODES for each NAME)
gt;
gt; Can anyone think of a way to automatically delete/insert rows (ignoring
gt; that there may be info in CODES), so that NAME is every 6 rows??
gt;
gt; eg
gt;
gt; Bob 5 1 / 2 Rodeo Drive 5551245 Jane QWE
gt; Springfield ASD
gt; WA ZXC
gt; 76767 QAZ
gt; LKJ
gt; JHG
gt; OIU
gt; HUY
gt; Bob2 6 1 / 2 Rodeo Drive 5551245 Jane QWE
gt; Springfield
gt; WA
gt; 76767
gt;
gt;

Any suggestions on a macro that could do that?

The data is then saved as a csv then would be imported into an access
database.

exceluserforeman wrote:

gt; You need vba code to do it. How does the data be inserted anyway?
gt;
gt; www.geocities.com/excelmarksway
gt;
gt;
gt; quot;Mr_Flibblequot; wrote:
gt;
gt; gt; Hi All,
gt; gt;
gt; gt; I have a problem formatting an existing Excel spreadsheet into a format
gt; gt; acceptable by a database which needs each NAME cell to be separated by
gt; gt; 5 blank rows.
gt; gt;
gt; gt; I have the following fields...
gt; gt;
gt; gt; NAME, ID, ADDRESS, TELEPHONE/FAX/EMAIL, CONTACT, CODES
gt; gt;
gt; gt; (where there can be 1-8 rows of CODES for each NAME)
gt; gt;
gt; gt; Can anyone think of a way to automatically delete/insert rows (ignoring
gt; gt; that there may be info in CODES), so that NAME is every 6 rows??
gt; gt;
gt; gt; eg
gt; gt;
gt; gt; Bob 5 1 / 2 Rodeo Drive 5551245 Jane QWE
gt; gt; Springfield ASD
gt; gt; WA ZXC
gt; gt; 76767 QAZ
gt; gt; LKJ
gt; gt; JHG
gt; gt; OIU
gt; gt; HUY
gt; gt; Bob2 6 1 / 2 Rodeo Drive 5551245 Jane QWE
gt; gt; Springfield
gt; gt; WA
gt; gt; 76767
gt; gt;
gt; gt;You say 5 rows from the name yet 8 rows could be part of the data.

You need to tell me how the data is inserted. Is it imported? Or is the data
generated dependant on user interaction (eg: by userform)?
Due to current time restrictions, I may not be able to respond until early
next week.

Signing off ....quot;Mr_Flibblequot; wrote:

gt; Any suggestions on a macro that could do that?
gt;
gt; The data is then saved as a csv then would be imported into an access
gt; database.
gt;
gt; exceluserforeman wrote:
gt;
gt; gt; You need vba code to do it. How does the data be inserted anyway?
gt; gt;
gt; gt; www.geocities.com/excelmarksway
gt; gt;
gt; gt;
gt; gt; quot;Mr_Flibblequot; wrote:
gt; gt;
gt; gt; gt; Hi All,
gt; gt; gt;
gt; gt; gt; I have a problem formatting an existing Excel spreadsheet into a format
gt; gt; gt; acceptable by a database which needs each NAME cell to be separated by
gt; gt; gt; 5 blank rows.
gt; gt; gt;
gt; gt; gt; I have the following fields...
gt; gt; gt;
gt; gt; gt; NAME, ID, ADDRESS, TELEPHONE/FAX/EMAIL, CONTACT, CODES
gt; gt; gt;
gt; gt; gt; (where there can be 1-8 rows of CODES for each NAME)
gt; gt; gt;
gt; gt; gt; Can anyone think of a way to automatically delete/insert rows (ignoring
gt; gt; gt; that there may be info in CODES), so that NAME is every 6 rows??
gt; gt; gt;
gt; gt; gt; eg
gt; gt; gt;
gt; gt; gt; Bob 5 1 / 2 Rodeo Drive 5551245 Jane QWE
gt; gt; gt; Springfield ASD
gt; gt; gt; WA ZXC
gt; gt; gt; 76767 QAZ
gt; gt; gt; LKJ
gt; gt; gt; JHG
gt; gt; gt; OIU
gt; gt; gt; HUY
gt; gt; gt; Bob2 6 1 / 2 Rodeo Drive 5551245 Jane QWE
gt; gt; gt; Springfield
gt; gt; gt; WA
gt; gt; gt; 76767
gt; gt; gt;
gt; gt; gt;
gt;
gt;

Yes, that database import process only allows for the quot;Namequot; row and 5
rows after that. The 6th row needs to be the quot;Namequot; row and so forth. I
realise this means discarding CODES fields, but it's the only way.

What I think would work would be a macro that could automatically count
the blank/empty cells between the Name rows and then delete/insert rows
(which may not be completely empty) before the next Name row to make
them 6 rows apart. Are there some functions that sound like they could
do this?

A little more background to the dilemma is that I was given the
spreadsheet with all the existing data in it. If there's some way of
correctly formatting it then I should be able to use an existing import
tool to put it into Access where it will only be used for reference
purposes (ie users will not enter any extra data).

I would be really grateful for any suggestions......Hello again,

You say that the database import process only allows for the quot;Namequot; row and 5
gt; rows after that. Can't you make it 9 rows after the name? Then you can be assured of the row segmentation.

What type of quot;database import processquot; are you using?

If the source file is a .csv or .txt or .dat and not an application
proprietory document like .xls or .doc or .pdf then you can import it with
excel either file open method or Data gt; Import external data gt;..

You can then determine the fields yourself.

Otherwise I could write an import file program that reads each line into
specific cell
locations. I need to look at the file first to determine its layout ie
delimiters ...Example1: quot;blah,blah,blahquot;

Example2: quot;blahquot; quot;blahquot; quot;blahquot;

Example3: blah,blah,blah

Example4: blah blah blah

etc......send to

see my stuff at:
www.geocities.com/excelmarksway

- - Mark

quot;Mr_Flibblequot; wrote:

gt; Yes, that database import process only allows for the quot;Namequot; row and 5
gt; rows after that. The 6th row needs to be the quot;Namequot; row and so forth. I
gt; realise this means discarding CODES fields, but it's the only way.
gt;
gt; What I think would work would be a macro that could automatically count
gt; the blank/empty cells between the Name rows and then delete/insert rows
gt; (which may not be completely empty) before the next Name row to make
gt; them 6 rows apart. Are there some functions that sound like they could
gt; do this?
gt;
gt; A little more background to the dilemma is that I was given the
gt; spreadsheet with all the existing data in it. If there's some way of
gt; correctly formatting it then I should be able to use an existing import
gt; tool to put it into Access where it will only be used for reference
gt; purposes (ie users will not enter any extra data).
gt;
gt; I would be really grateful for any suggestions......
gt;
gt;