Excel -> Access Import Bizarreness

A guy gave me an Excel file that he needs me to covert to XML.  No problem. 
Slurp it into a database and use a DataSet.WriteXML() call to turn it into 
an XML file.

But I can't import it into Access because it has "Merged Cells".  For 
example, data that should look like this:

ColA    ColB
-----    -----
Rec1    this
Rec2    this
Rec3    this
Rec4    that
Rec5    Other

The three "this" cells have been merged into one tall cell:

ColA    ColB
-----    -----
Rec1    this
Rec2
Rec3
Rec4    that
Rec5    Other

I need to "unmerge" these, but I know almost nothing about Excel.  Can 
someone show me the magic thing to do?

-- 
Peace & happy computing,

Mike Labosh, MCSD
"I have no choice but to believe in free will." 


0
mlabosh (6)
11/8/2004 3:34:22 PM
excel 39879 articles. 2 followers. Follow

7 Replies
448 Views

Similar Articles

[PageSpeed] 28

Range("D8:E10").mergecells = false

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Mike Labosh" <mlabosh@hotmail.com> wrote in message
news:%23vEeshaxEHA.2572@tk2msftngp13.phx.gbl...
> A guy gave me an Excel file that he needs me to covert to XML.  No
problem.
> Slurp it into a database and use a DataSet.WriteXML() call to turn it into
> an XML file.
>
> But I can't import it into Access because it has "Merged Cells".  For
> example, data that should look like this:
>
> ColA    ColB
> -----    -----
> Rec1    this
> Rec2    this
> Rec3    this
> Rec4    that
> Rec5    Other
>
> The three "this" cells have been merged into one tall cell:
>
> ColA    ColB
> -----    -----
> Rec1    this
> Rec2
> Rec3
> Rec4    that
> Rec5    Other
>
> I need to "unmerge" these, but I know almost nothing about Excel.  Can
> someone show me the magic thing to do?
>
> -- 
> Peace & happy computing,
>
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
>
>


0
Don
11/8/2004 4:35:21 PM
> Range("D8:E10").mergecells = false

EEEEEWWWWW!!!!!  That's awful.  This file is miles long and has hundreds of 
instances of merged values.  I finally found the checkbox in the Format 
Cells dialog, but either manually clicking all these things or coding their 
cell addresses is going to be supremely painful.  I guess I'm S.O.L. ?
-- 
Peace & happy computing,

Mike Labosh, MCSD
"I have no choice but to believe in free will." 


0
mlabosh (6)
11/8/2004 4:50:27 PM
OK>select the cells button (top of the 1 and left of the A)>right
click>format>alingment>uncheck merge cells

-- 
Don Guillett
SalesAid Software
donaldb@281.com
"Mike Labosh" <mlabosh@hotmail.com> wrote in message
news:OWJhNMbxEHA.4004@tk2msftngp13.phx.gbl...
> > Range("D8:E10").mergecells = false
>
> EEEEEWWWWW!!!!!  That's awful.  This file is miles long and has hundreds
of
> instances of merged values.  I finally found the checkbox in the Format
> Cells dialog, but either manually clicking all these things or coding
their
> cell addresses is going to be supremely painful.  I guess I'm S.O.L. ?
> -- 
> Peace & happy computing,
>
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
>
>


0
Don
11/8/2004 5:00:09 PM
> OK>select the cells button (top of the 1 and left of the A)>right
> click>format>alingment>uncheck merge cells

Outstanding!  But the blocks of cells that were merged didn't get the values 
"fill-down" copied into them.  Is there another cool trick that can be 
coupled with the above technique that will also "fill-down" the values?
-- 
Peace & happy computing,

Mike Labosh, MCSD
"I have no choice but to believe in free will." 


0
mlabosh (6)
11/8/2004 5:17:32 PM
Debra Dalgleish has some techniques to fill empty cells at:
http://www.contextures.com/xlDataEntry02.html

Mike Labosh wrote:
> 
> > OK>select the cells button (top of the 1 and left of the A)>right
> > click>format>alingment>uncheck merge cells
> 
> Outstanding!  But the blocks of cells that were merged didn't get the values
> "fill-down" copied into them.  Is there another cool trick that can be
> coupled with the above technique that will also "fill-down" the values?
> --
> Peace & happy computing,
> 
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."

-- 

Dave Peterson
ec35720@netscape.com
0
ec357208423 (183)
11/8/2004 10:13:41 PM
My approach:

Write a formula to the right that is something like  =if(A2="", A1, A2) in 
cell B2, copy to the bottom.  Then copy, paste special values to remove the 
formula.  Then delete the original column.

I have had to do this when referencing pivot tables before.



"Mike Labosh" <mlabosh@hotmail.com> wrote in message 
news:e5O%23VbbxEHA.1956@TK2MSFTNGP14.phx.gbl...
>> OK>select the cells button (top of the 1 and left of the A)>right
>> click>format>alingment>uncheck merge cells
>
> Outstanding!  But the blocks of cells that were merged didn't get the 
> values "fill-down" copied into them.  Is there another cool trick that can 
> be coupled with the above technique that will also "fill-down" the values?
> -- 
> Peace & happy computing,
>
> Mike Labosh, MCSD
> "I have no choice but to believe in free will."
> 


0
tbenedict (10)
11/8/2004 11:30:55 PM
And if that data is in a pivottable, it'll have to be converted to values first.

Travis Benedict wrote:
> 
> My approach:
> 
> Write a formula to the right that is something like  =if(A2="", A1, A2) in
> cell B2, copy to the bottom.  Then copy, paste special values to remove the
> formula.  Then delete the original column.
> 
> I have had to do this when referencing pivot tables before.
> 
> "Mike Labosh" <mlabosh@hotmail.com> wrote in message
> news:e5O%23VbbxEHA.1956@TK2MSFTNGP14.phx.gbl...
> >> OK>select the cells button (top of the 1 and left of the A)>right
> >> click>format>alingment>uncheck merge cells
> >
> > Outstanding!  But the blocks of cells that were merged didn't get the
> > values "fill-down" copied into them.  Is there another cool trick that can
> > be coupled with the above technique that will also "fill-down" the values?
> > --
> > Peace & happy computing,
> >
> > Mike Labosh, MCSD
> > "I have no choice but to believe in free will."
> >

-- 

Dave Peterson
ec35720@netscape.com
0
ec357208423 (183)
11/9/2004 12:23:43 AM
Reply:

Similar Artilces:

Working with Excel #2
Dear friends! I have the following problem - I am needing to represent in Excel a number with 20 (or more) digits. How can I do this? Many thanks! Hi Igor2005, > I have the following problem - I am needing to represent in Excel a number > with 20 (or more) digits. How can I do this? > That is only possible with either: - an addin (search google, there should be something for this, I recall seeing it but misplaced the link). - or by formatting the cell as text (but you won't be able to do math with the number easily then) Regards, Jan Karel Pieterse Excel MVP www.jkp-ad...

Excel 2007 and XML with inline schema
Hi. I have XML data that I want to be more user-friendly when its opened in excel. The user can create a web Query which will return the xml to them - ( pointing to the URL with the XML information)- and they can refresh the query each time they open the document in excel. Unfortunately- they have to format the data in excel manually. I wanted to associate an inline schema with the data to format the date cells, number cells etc. but the schema is not recognised in Excel. I have been just trying to get excel to recognise an inline schema- is this possible in the Excel? Any information regardi...

Practical Jokes in Excel?
Hello - What practical jokes have you played with Excel, either VBA or just built-in features? With all of the creativity on this Board, I figure there's got to be quite a few gems out there ... One of my coworkers thinks he knows it all, so I'd like to punk him ... my initial thought was a macro that would speak something (using text-to-speech) or play a sound when a specific key is pressed (say, the SHIFT or CTRL keys). I have absolutely no idea where to start with this ... thoughts? Looking forward to seeing what's been done before! //ray On Mar 2, 12:43=...

Excel 2000- protect one column- can only be modified by one person
I need to lock one column that can only be modified by one person. Can this be done? Joe There's nothing in Excel 2000 to set user range permissions. That feature was added in Excel 2002. fyi. -- Jim Rech Excel MVP "lunker55" <joec@shipwaystairs.com> wrote in message news:ObkUI8VAEHA.3936@TK2MSFTNGP11.phx.gbl... | I need to lock one column that can only be modified by one person. Can this | be done? | | Joe | | Thanks Jim "Jim Rech" <jrrech@hotmail.com> wrote in message news:O6t8%23rWAEHA.1420@TK2MSFTNGP11.phx.gbl... > There's nothing in E...

Problem Importing Data Using Wizard
I have a text file with contents that look like this: 02/06/2006 16.02.00 This is the date and time. I want this data imported into one cell and treated as a text string. When I use DATA-->Import External Data-->Import Data, select the text file and the required wizard entries, every time it adds a column to the left of the column I'm working with, and inserts the data in this column to the left of the cell where I want it. Does anyone understand what's going on? Thanks, Carroll Rinehart When you get to where the columns are decided, is there an extra column line? You ca...

outlook blocked access to potentially unsafe item, archive .pst
Tried to open archive in outlook 2003 said file was enormous. "Egro" <Egro@discussions.microsoft.com> wrote in message news:F8BC04C1-B005-43CF-ABA5-9C171EA74B9F@microsoft.com... > Tried to open archive in outlook 2003 said file was enormous. As far as I can tell, there is no error message in Outlook 2003 that states a file "is enormous". You should zip a PST before sending or rename it to, say, ".psx" or ".ps_" before you send it. The recipient can undo the steps you took to send it. -- Brian Tillman [MVP-Outlook] ...

Access Mulit users in form keep stumbling over each other
I've got a front end Access database that has the form and a backend Accessdatabase that has the data. I would like mulitple users to be able to enter information into the form but they keep stumbling on each others work when they are doing so. My database is very simple, the table has 2 columns one has the account number of customers and the other is where the workers will fill in Yes or No. The users open the form copy the account number, enter it into a program, and review it. If they account is positive for what they are looking for, they mark yes or no and go onto the next record....

Excel loads slow
We just recently upgraded to XP SP2 with Office XP SP3. Before, on 2000, our users did not have a problem opening files from a certain directory. Now, while in Excel, the click the open folder and it takes 30-45 seconds for them to navigate to each folder in the directory. Any thoughts as to why XP is slower when drilling down through the folders? Thanks. Right-click on the My Computer icon on the Windows desktop, left-click on Disconnect Network Drive, and disconnect any mapped drives that are not currently available. Shane wrote: > We just recently upgraded to XP SP2 with Off...

Outlook blocked access to the following potentially unsafe attachment blah.url
How do I lower security settings in order to receive all attachments?? What version of Outlook? --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. After searching google.groups.com and finding no answer: Googy <googy55@pacbell.net> asked: | How do I lower security settings in order to receive all attachments?? Me to. I have Outlook XP and getting quit tired of not being able to get need attachments. I hope somebody has the answer. OL 2000 SP-3 Corproate/Workgroup with security update Thanks GG "Milly Staples [MVP - Outlook...

project mgmt add on for excel
Is there a project management add on for excel inc. gant charts? ...

Excel For Mac 04-23-10
Several people in our company recently started to use MACs. They cannot open ..xlsx files we have created in Excel 2007. Does anyone know what the solution is to this issue. Thanks. We do not need to run macros. But they just need to open the files. Mike H. - Be sure Mac Excel 2008 has the most recent updates, currently 12.2.4, and then run the Mac disk utility to repair permissions. If you have the problem when you're using 12.2.4, I suggest posting in the very active newsgroup microsoft.public.mac.office.excel. To avoid offending some Mac users, be sure to re...

Copy a word doc to excel and hyperlink data between the 2.
I have a chart on an excel document and a long word document explaining the data in the chart. I would like to place the word document under the chart in excel. Then I would like to hyperlink the data from the chart to specific places in the word document. Help.....I have been trying to figure it out for days. Thank you. ...

Attaching an email or a scanned document to an Access Report
In my original database (now two years old), I added a paragraph of text in a memo field in a record. Now, after a group reorganization, I will be getting additional e-mailed information and/or hard copy that has to become part of that record. I am planning to scan the hard-copy to create an attachable document and/or open the email and somehow include the attachment to the Access record. Does anyone have any suggestions or examples? Would hyperlinking be the way to go? Sorry, I meant to mention I'm using Access 2003 and the documents are usually 2-3 pages long. "Help Elimin...

Query to Access : does'nt see tables
Office 2000 SP3 FR From Excel / MS Query, I want to read data in Access tables. I choose the mdb file and so MS Query displays this error message (translated) : "This data source does'nt have any visible table". Original in French : "Cette source de donn�es ne contient aucune table visible." I transfert my data to a new mdb file : it work's few times (!), then the error message appears again. How to resolve this problem ? Jacques. Gotcha ! MS Query does'nt accept a file name with 2 dot inside ! Example : MyFile.mdb --> Ok My.File.mdb --> "...

bmp files in Excel Hyperlinks open with wrong program
I have a list of tools in Excel, and I have used Excel's hyperlinking feature to allow displaying a picture of the tool by clicking the link. They always open with Paint, regardless of what I do in the "Folder Options >> File Types" box. I have customized the .BMP file type by adding "Edit with Paint" and "Edit with PhotoEditor" entries to the default settings. Does Excel look for the entry actually labeled "Open" or does it just grab the first entry from the list? (in this case, "Edit with Paint" is first, because they are ...

Excel 2007 convert into tab delimeter text file with extra tab spa
Hi , Sub: I have excel 2007 trying to save as tab delimeted text file having an issue. I have saved my excel 2007 as separate Tab delimited Text files. I see extra tabs stored in the tab delimited text files. But when I do the same with excel 2003 it works fine there is not extra tabs stored in my tab delimited text file. My excel 2007 conveted to tab delimited text file looks like as shown below(Extra two tabs at the end of the each record). Data0<Tab>data1<Tab><Tab> Data2<Tab>data3<Tab><Tab> Appreciate your help in advance. Rega...

Bug Report
On Mac ----------- Construct linear plot in Excel including: 1. Title 2. X-axis label 3. Y-axis label Copy and Paste into Word Save and send to PC On PC ---------- Open file in Word Notice Y-axis label is gone I'm a Biochemistry student who uses Excel to do lots of number crunching. I use a 1.25G Powerbook G4 with OS X 10.4.3 and the newest version of Office. I copy plots from Excel to Word often when writing reports without any problems. Recently, I've began submitting reports electronically via email and my professor uses a PC. When he opens my files, Y-axis labels are mis...

RMS needs to add an Import Utility to the program
I think I speak for alot of people here. RMS should add an import Utility to the program. Every body needs to import some type of file from time to time. You pay enoght money for this and they know from the start that you will be importing data from your old POS system. Or like in my case, every year I have new items or a new supplier. and for me to get an Add in that may or maynot work is not good enoght. Jose -- Reynold Cycle I agree totally but, for me, the main attraction of RMS over similar solutions was the open SQL Server database and the ability to customise. You can downlo...

Temporary Excel files.
Hi, does anyone know where Excel stores its temp files to managing multiple opened copies of the same workbook? Word stores its lock files in same directory of the opened document, but Excel? Thanks in advance for the answers. Alex On a single user (non-networked pc), excel will put a few files in your windows temp folder. But I think it does more than just that. (Just guessing!) When the workbook is on a network, excel/windows is smart enough to be able to tell that second person the the file is in use. If the only indicator were on the first user's temp file, then the seco...

Add more than one series to a pivot chart using VB MS Access continued...
I'm trying to programmatically create a stacked bar pivot chart. Using "Programming Microsoft Office Access 2003" by Rick Dobson, I've created the chart. However, it doesn't distinguish between the different values for the series. Does anyone have any suggestions on how to create a chart using one column containing three values for the series? Essentially, this is a continuation of a previous post: http://groups.google.com/group/microsoft.public.access.formscoding/browse_thread/thread/e23f3506a6d561a0/674295d410a71cf9%23674295d410a71cf9" Any help is greatly appre...

Import into Access from Excel, passing a variable as the field...
Hello all, I have an Excel spreadsheet with a header that contains the exact same 30 names as the corresponding 30 fields in an Access DB table, and Row 2 below the Header contains the data I want to import. Instead, of physically typing all 30 fields in the code, I have a macro that loops through each "field" name that it got from the spreadsheet header. When I run this macro I get "run-time error '3265' Item not found in this collection', however, when I specify the actual field Name manually it works. This what I have: Dim iColumn As Integer Dim FieldName As ...

VBA to export large tables from Excel to SQL Server
I have spreadsheets that I distribute to users. I want Excel VBA to export large tables (2000+ rows) into a remote SQL Server 2005. I am able to successfully do this with the following generic code, but it is too slow: Dim con As ADODB.Connection Set con = New ADODB.Connection con.Open "Driver={SQL Server};SERVER=<server>;DATABASE=TestSample;UID=<id>;PWD=<pw>;" con.Execute "INSERT INTO ... SELECT * FROM ..." 'Looping this for each row It takes over 10 minutes because it does it one line at a time. I've also tried l...

Outlook Web Access
Hello all, Outlook Web Access has been rolled out to my organization. It's particularly useful in areas where PCs are shared among numerous people and are logged onto the network with generic IDs. The problem I'm experiencing is that when an account is ready for a password change, there is no notification to the user before logging into Outlook Web Access. This is a problem because after three checks of their email, they can no longer get into their email. Now I'm not an Exchange server person, but I need to get some ideas to present to the Networking Staff. Does anyo...

when inserting a chart excel is saying i cannot add any new fonts
When I attempt to insert a chart i am getting a message which says,"no new fonts may be added to this workbook". I am adding it to a billing process and I did the first 100 or so before it began this doing this, I am not adding any new fonts but I am getting this message, any suggestions on what is the cause so I can overcome it? I've described the problem and fix here: Error: "No more new fonts may be applied in this workbook" http://peltiertech.com/Excel/Charts/FixFonts.html - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltier...

Try these important update
--aggpexytarh Content-Type: multipart/related; boundary="ovpxzxjley"; type="multipart/alternative" --ovpxzxjley Content-Type: multipart/alternative; boundary="prvgddqc" --prvgddqc Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Customer this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect your c...