[mso] Re: Excel Named Range
- From: "Ray Blake" <ray@xxxxxxxxx>
- To: <mso@xxxxxxxxxxxxx>
- Date: Mon, 20 Oct 2003 21:50:24 +0100
Glenda,
OK. The code's exactly right, except that you need to wrap it in a
subrountine. Put Sub ImportExcel at the top, and make sure there's and
End Sub at the bottom of all the code. You can make it run in one of a
number of ways. If your Access application already has some sort of
control or switchboard form, you could put a button there and link it to
your code. That's what I did, but I also had a browse control because
the file I wanted to import from was different every time.
Ray
_____________
This email is from Ray Blake, Head of Software Design, GR Business
Process Solutions. It is confidential and intended for the addressee
only. The contents are private and may be legally privileged. If you
receive this email in error we would be grateful if you would advise the
sender and delete the email from your system.
For more information on the services that we offer please visit us at
our website: - www.grbps.com
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Glenda Wells
Sent: 20 October 2003 21:45
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Excel Named Range
Hi Ray.
I've done everything you said but please forgive my ignorance.....where
does the "little code" go?
I put this into a module but I have no idea if it's right, what it means
or how to make it run.
impEmail is the name of the import table, "D:\Old..." is the path of the
original email worksheet, expEmail is the name of the data range within
the Excel worksheet, apnEmail is the name of the append query, delEmail
is the name of the query that clears the import table data.
Option Compare Database
DoCmd.TransferSpreadsheet acImport, , "impEmail",
"D:\OldStuff\Admissions\BaseData\email.xls", True, "expEmail"
DoCmd.OpenQuery ("apnEmail")
DoCmd.OpenQuery ("delEmail")
-----Original Message-----
From: Ray Blake [mailto:ray@xxxxxxxxx]=20
Sent: Monday, October 20, 2003 2:57 PM
To: mso@xxxxxxxxxxxxx
Subject: [mso] Re: Excel Named Range
Glenda,
I've just done something similar to this myself. Let me reassure you it
can be done, and you can lose those pesky empty rows, too. In fact there
are two ways.
The first is to use dynamic range names in Excel to eliminate empty rows
before the Access import, but if you have empty rows in the middle of
the data this won't help anyway.
The second way involves using an import table, and an append query. This
is going to sound complicated, but it's the best way I found to get it
done and it works like a dream.
Set up your import table with the same structure as the table you want
ultimately to put the data in. Make sure you range in Excel has column
heads which match the field names in the table.
Now design a query based on the import table. Lift in all the fields and
decide on a field you'll use to check for blanks. For that field add the
criterion 'Is Not Null' and make this an append query, writing the data
to your main table. Finally you need a delete query to empty the import
table.
All you need the is a little code to put it all together. This will do
it:
DoCmd.TransferSpreadsheet acImport, , "imptblPersonal", True,
"expEmail"
DoCmd.OpenQuery ("apqryPersonal")
DoCmd.OpenQuery ("deqryPersonal")
imptblPersonal is the import table, apqryPersonal the append query and
deqryPersonal the delete query. ExportPersonal is the Excel range.
Newfileandpath is a string variable holding the location of the Excel
file.
Ray
_____________
This email is from Ray Blake, Head of Software Design, GR Business
Process Solutions. It is confidential and intended for the addressee
only. The contents are private and may be legally privileged. If you
receive this email in error we would be grateful if you would advise the
sender and delete the email from your system.
For more information on the services that we offer please visit us at
our website: - www.grbps.com=20
-----Original Message-----
From: mso-bounce@xxxxxxxxxxxxx [mailto:mso-bounce@xxxxxxxxxxxxx] On
Behalf Of Glenda Wells
Sent: 20 October 2003 16:57
To: mso@xxxxxxxxxxxxx
Subject: [mso] Excel Named Range
Hi All.
I get a large Excel Workbook containing about 20 worksheets.
I import each worksheet to an Access table.
My first question about this is can I link the Access table to a named
worksheet range? I think the answer here is yes which leads to my next
question.
How can I make sure the named range includes only rows and columns that
have data in them without having to reapply the name every time?
Here's the problem. The Excel Workbook is actually linked to a database
using Microsoft Query. When I refresh the data, it looks great with
each column and row of each worksheet containing data.
When I try to link an Access table to one of the worksheets, I almost
always get excess rows and columns not containing data....that is, empty
columns and rows. For example, a worksheet containing 40 rows and 10
columns in Excel will end up with 702 rows and 23 columns when linked in
Access. =20
My work around to this is to copy only valid rows and columns of each
worksheet in the workbook to it's own worksheet then import that
worksheet to an existing Access table. This means I have to open the
master workbook, highlight valid rows and columns, open the
corresponding stand alone worksheet and clear it out then copy the valid
rows and columns from the master. Then, open Access and empty the
corresponding table then import the "new" table.
This is time consuming and I'm looking for a better, more efficient
method. Ideally, I think, I'd just like to link the Access tables to
the refreshed ranges in the master workbook and be done with it and I
would if it weren't for those mysterious empty rows and columns.
Any advice or suggestions appreciated.
/g
ps, this is being done in Office XP
*************************************************************
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=20
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.
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). //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=20
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the
quotes) in the subject line.
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). //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 with the word "unsubscribe" (without the
quotes) in the subject line.
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).
//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 with the word "unsubscribe" (without the quotes) in
the subject line.
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).
//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:
