I'm trying to import an Excel spreadsheet into an existing Access
table using Office 2003. Ultimately, the plan is to do it
programmaticall y using TransferSpreads heet, but to check that the file
has no problems, I've done it manually with the Import Spreadsheet
wizard.
The worksheet has 43 rows, and I import a named range defined as
"=Sheet1!$C:$E" . The import works, but I get a table with 64K rows,
all but 43 being blank!
Is there a way to define the range so that only filled rows will be
imported? I don't want to use the last row's address explicitly,
because I will be importing worksheets of varying lengths.
Any help gratefully received (Apologies if this a no-brainer: I don't
'do' Excel as a rule)! 3 5325
Look at this - not my work but what you are looking for:
Dynamic Ranges
It is often useful to create a name that refers to a range of cells, where
the range depends on the content of the cells. For example, you may want a
name that refers to the first N non-blank entries in column A. Excel's
Name tool allows you to do this. For example, creating a name called
DynaRange, referring to
=OFFSET(Sheet1! $A$1,0,0,COUNTA (Sheet1!$A:$A), 1)
If the first 20 rows of column A contain data (and the rest are blank),
DynaRange will refer to the range A1:A20
See the on-line help for the =OFFSET function for a description of the
arguments. Setting the Width argument to 2 will allow us to use this name
in a =VLOOKUP function
=OFFSET(Sheet1! $A$1,0,0,COUNTA (Sheet1!$A:$A), 2)
Then, call VLOOKUP with the DynaRange argument for the lookup range:
=VLOOKUP(C1, DynaRange, 2)
As data is added to columns A and B, the range search by VLOOKUP will extend
to include the new data.
Download a workbook illustrating dynamic ranges.
<D.*****@ed.ac. ukwrote in message
news:11******** *************@w 3g2000hsg.googl egroups.com...
I'm trying to import an Excel spreadsheet into an existing Access
table using Office 2003. Ultimately, the plan is to do it
programmaticall y using TransferSpreads heet, but to check that the file
has no problems, I've done it manually with the Import Spreadsheet
wizard.
The worksheet has 43 rows, and I import a named range defined as
"=Sheet1!$C:$E" . The import works, but I get a table with 64K rows,
all but 43 being blank!
Is there a way to define the range so that only filled rows will be
imported? I don't want to use the last row's address explicitly,
because I will be importing worksheets of varying lengths.
Any help gratefully received (Apologies if this a no-brainer: I don't
'do' Excel as a rule)!
<D.*****@ed.ac. ukwrote in message
news:11******** *************@w 3g2000hsg.googl egroups.com...
I'm trying to import an Excel spreadsheet into an existing Access
table using Office 2003. Ultimately, the plan is to do it
programmaticall y using TransferSpreads heet, but to check that the file
has no problems, I've done it manually with the Import Spreadsheet
wizard.
The worksheet has 43 rows, and I import a named range defined as
"=Sheet1!$C:$E" . The import works, but I get a table with 64K rows,
all but 43 being blank!
Is there a way to define the range so that only filled rows will be
imported? I don't want to use the last row's address explicitly,
because I will be importing worksheets of varying lengths.
Any help gratefully received (Apologies if this a no-brainer: I don't
'do' Excel as a rule)!
If your data is surrounded by blank cells then this should work:
Dim myRange as Range
Set myRange = Range("A1").Cur rentRegion
That assumes that cell A1 is in your region.
Keith. www.keithwilby.com
On 9 Jul, 11:36, "Kc-Mass" <connearney_AT_ comcast_PERIOD_ netwrote:
Look at this - not my work but what you are looking for:
Dynamic Ranges
It is often useful to create a name that refers to a range of cells, where
the range depends on the content of the cells. For example, you may want a
name that refers to the first N non-blank entries in column A. Excel's
Name tool allows you to do this. For example, creating a name called
DynaRange, referring to
=OFFSET(Sheet1! $A$1,0,0,COUNTA (Sheet1!$A:$A), 1)
If the first 20 rows of column A contain data (and the rest are blank),
DynaRange will refer to the range A1:A20
See the on-line help for the =OFFSET function for a description of the
arguments. Setting the Width argument to 2 will allow us to use this name
in a =VLOOKUP function
=OFFSET(Sheet1! $A$1,0,0,COUNTA (Sheet1!$A:$A), 2)
Then, call VLOOKUP with the DynaRange argument for the lookup range:
=VLOOKUP(C1, DynaRange, 2)
As data is added to columns A and B, the range search by VLOOKUP will extend
to include the new data.
Download a workbook illustrating dynamic ranges.
<D.St...@ed.ac. ukwrote in message
news:11******** *************@w 3g2000hsg.googl egroups.com...
I'm trying to import an Excel spreadsheet into an existing Access
table using Office 2003. Ultimately, the plan is to do it
programmaticall y using TransferSpreads heet, but to check that the file
has no problems, I've done it manually with the Import Spreadsheet
wizard.
The worksheet has 43 rows, and I import a named range defined as
"=Sheet1!$C:$E" . The import works, but I get a table with 64K rows,
all but 43 being blank!
Is there a way to define the range so that only filled rows will be
imported? I don't want to use the last row's address explicitly,
because I will be importing worksheets of varying lengths.
Any help gratefully received (Apologies if this a no-brainer: I don't
'do' Excel as a rule)!
Thanks for this, KC-Mass, I thought it sounded just what I need.
BUT... there is a snag! I have set up a named range similar to your
DynaRange using Insert|Name|Def ine. However, this name does not appear
in the Name box dropdown list, and (more crucially) nor does it appear
in the list of named ranges within Access' Import Spreadsheet wizard.
So, although the name appears to be defined in Excel, it seems I can't
use a dynamic range in Access. (Incidentally, the Import Spreadsheet
wizard doesn't display ranges based on non-contiguous columns,
either).
I wanted to avoid using Office automatiion if there was an easier way,
but hope is fading fast!
Cheers,
Dave This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Little PussyCat |
last post by:
Hello,
I nee to write something that will transfer excel data into an SQL Server
table.
I have for another database application I wrote have it importing Excel
spreadsheet data using cell by cell, row by row method. This is fully
automated so the user can choose whatever spreadsheet they want to import
and press a button which sits on a VB6 frontend.
This has been good for that situsation but it can be very slow when there
|
by: Edward S |
last post by:
I budget for a Project in an Excel sheet as illustrated below. The
months below are usually a 2 year period i.e. 24 months, though it
could be over 24 months depending upon a Project. I then need to
input this in an Access database, where I do a comparison with the
Actual cost. The table “TblBudget” in Access is made of 4
fields, namely: (1) CostElement (2) CostCenter (3) Month (4) Amount$.
At the moment this method is very cumbersome....
|
by: Mr. Bungle |
last post by:
When importing excel from access I am fully aware that one can import
directly into a table. Can you get as specific via code or something
to import data from an excel sheet to a FORM (Not Table) through a
command button. For example: cell C5 on the selected excel spread
sheet = textbox2 in the current form.
This is for a service repair type database. The service technicians
work out in the field and currently create their service orders...
|
by: Don W. Reynolds |
last post by:
Hi All,
I am sent an excel spreadsheet on a daily basis. Sometimes it contains 10
rows; sometimes it contains over 5000 rows. I copy this spreadsheet into
another spreadsheet and verify the format of the data (some data is missing,
which I can add, some 15 digit data needs to be text rather than numeric,
sometimes the incoming numbers are not consistent and need leading digits,
etc.). I then import this data (from the second...
|
by: Mihael |
last post by:
Need help when importing excel sheet with cca 8.000 records in a new table I
get message subscript out of range. why?
| |
by: nutthatch |
last post by:
I want to be able to import an Excel spreadsheet into Access 2K using
the macro command Transferspreadsheet.
However, the file I am importing (over which I have no control)
contains some records that are "dirty" i.e. the field contents do not
comply with the expected format (date/time) and they end up in a
seperate table of import errors. (The records in "error" are actually
empty fields.)
This is a regular event and I do not want to...
|
by: jillandgordon |
last post by:
I am trying to import an excel file into Access 97. It looks perfectly all
right but, every time I try to import it, I get to the lst step and am told
that it was not imported due to an error. There is no further explanation.
What are the kinds of things that make this happen?
Thanks from an obvious rookie.
Gordon
|
by: Geoff Jones |
last post by:
Hi
I have a question which I hope somebody can answer. I have written a VB
application with which I want to import an Excel file, analyze the data
within it and do some calculations. There are in fact five sheets in the
Excel file. My original idea was to import the file into access and create a
database file; which I did and worked beautifully. It generated five tables
in the database as expected.
However, I then thought why not...
|
by: DrewYK |
last post by:
I have the project that may never end in front of me.
I am creating a routine that will take SpreadSheets from Excel and
bring them into Access.
I am not using any "DoCmd"s because the goal is for the import code
to be moved to a stand alone VB app which will use the Access DB as a
workspace to process the data from the spreadsheets. Quite honestly,
done right this may not even require Access or Excel to be on the
users machine. ...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |