[mso] Re: Pasting into merged cells WAS: Merging Excel cells with Access data

David-

The macro will do the work for you. I would suggest coding the Macro to
create a new worksheet, name the worksheet, and then begin the insert of the
Access data in cell A1. This way when the Macro completes all your clients
will have a new worksheet with the same name, just different data. You can
then programmatically work with the against new worksheet.

-Anthony

-----Original Message-----
From: David Good - ANSYS Europe [mailto:david.good@xxxxxxxxx]
Sent: Friday, May 31, 2002 10:05 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Pasting into merged cells WAS: Merging Excel cells with
Access data



Cheers, this is good stuff, although I've a horrible feeling it will bring
up another (access unrelated) problem - although I haven't tested it yet as
I don't have the access dB:

When data is pasted into a merged cell from an external application I get
the error:

"Data on the Clipboard is not the same size and shape as the selected area."

Presumably I can code the macro below somewhere to get this data to either
do an unmerge, or a direct paste somehow. However am I missing an easy way
around this? (I know I can double click a cell, or enter this into the
formula bar - but this isn't good enough for some people :( ).

Did I just answer my own question?

Thanks,

Dave.

-----Original Message-----
From: Colli, Anthony G [mailto:Anthony.Colli@xxxxxxx] 
Sent: 31 May 2002 14:42
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Re: Merging Excel cells with Access data


David-

 Importing into Excel from Access is easy. But in your case with a
distributed application it can become quite complex. If you were to hard
code all the parameters to import some Access data, then all your clients
would have to have the same Access version, of the named database, in the
same location, with the same table names, with the same column names, and so
on. If any one of the parameters is incorrect the import will fail. 

 Having said that, this is the Excel Macro code that will import Access data
into Excel. It looks for a database in: H:\MyDocs\ named db1.mdb. It
executes a SELECT statement against the Access table Labels, and places the
results starting in the cell A12. 

 For more details on how this works check out the wizard in Data->Get
External Data.

Good Luck

-Anthony

----------------------------------------------------
Sub ImportAccess()

    With ActiveSheet.QueryTables.Add(Connection:= _
        "ODBC;DSN=MS Access
Database;DBQ=H:\MyDocs\db1.mdb;DefaultDir=H:\MyDocs;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;" _
        , Destination:=Range("A12"))
        .CommandText = Array( _
        "SELECT Labels.LabelID, Labels.`Label A`, Labels.`Label B`,
Labels.`Label Text`" & Chr(13) & "" & Chr(10) & "FROM `H:\MyDocs\db1`.Labels
Labels" _
        )
        .Name = "Query from MS Access Database_1"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

-----Original Message-----
From: David Good - ANSYS Europe [mailto:david.good@xxxxxxxxx]
Sent: Friday, May 31, 2002 7:37 AM
To: 'mso@xxxxxxxxxxxxx'
Subject: [mso] Merging Excel cells with Access data



I have another challenge set fourth.

I have designed an Excel sheet that does various dull order entry tasks and
activities. This form has now been rolled out to our resellers, some of who
wish to merge information in from their Access Databases (e.g. customer
name, address etc.). I have little (no) Access experience, but should
imagine that this (should) be relatively easy (?!). I've scouted around
websites for this kind of information, but not found anything relevant. Are
there any web pages, or general advice that I can take on board. I guess
what I'm looking for is something mega-simple for them - perhaps a formula
to go in a cell that's kinda =getfromaccess(contact). (If only it was this
easy...)

Thanks for your help,

Dave.
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files: http://www.smartgroups.com/vault/msofiles
*************************************************************
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe

Or, visit the group's homepage and use the dropdown menu.  This will also
allow you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation
with instructions.  Once you are a member of the files group, you can go
here to upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************


*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx?Subject=unsubscribe

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: