Re: Import 'Choice' Data into Access From Excel

Tech-Archive recommends: Fix windows errors by optimizing your registry

Null essentially means "Unknown". What would you expect 63 + Unknown to be?

Whenever there's a chance that the value in the recordset might be null, use
the Nz function to provide a default value:

If ws.Range("H" & rNum) > Nz(![lastactivity], "")

or

if Nz(![ptnamelong], "") <> ws.Range("G" & rNum)


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"DavidAtCaspian" <DavidAtCaspian@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B90DD299-BB05-4FBD-B0CD-2B91F03B3504@xxxxxxxxxxxxxxxx
Nearly forgot: Be very careful of null, as it seems to have a mind of its
(
or is someone's idea of a joke)

This one which is supposed only to update a date if newer

If ws.Range("H" & rNum) > ![lastactivity]

does not work if ![lastactiivity] is null. - So a null date is greater
than
any actual date.

And this one

if ![ptnamelong] <> ws.Range("G" & rNum)

returns false if ![ptnamelong] is null. So although null is clearly not
the
same as the value in the spread***, it is nevertheless neither equal to
it,
nor not equal to it.

No doubt someone at Micrososft will tell you that's the way it's supposed
to
be.

I have also done some running totals in the past where 63 + null actually
equalled null (although an MVP assured me that it didn't)


"DavidAtCaspian" wrote:

What I would do, and have been this morning (UK) is
Structure your access table the way you want it. (ie design it to have
the
fields you want)

Open a module and add excel as a reference.

You can then open your spread*** with
excel.application.workbooks.open("your file")

You can navigate round the *** using 'range' by a column letter, and a
variable for row number.
If you set for example rNum to your first row with data, you can pull in
one
at a time the cells you want into access.
When you have all the fields full for that record, add 1 to rNum and go
round the loop.

For a one off, you can see from excel what the biggest row number is.

Once you have everything in access, run a make table query just to give
another table showing what you do want. (or just bring in the whole lot
and
reduce with a make table query)
You can show it all off to your managers, and if you decide more columns
are
wanted from the spread***, it is easy enough to tweak your code and
produce
another clean copy.

Here is abit of code from mine earlier. - In my case someone has tarted
up
the top of the spread*** and I just work down column A till I find a
data
record (where position 5 is a hyphen (-). (Left out all the Dims for
brevity)
(BUT F must be a variant !!)

The first part pops up a dialog to select the spread***.
The password is stored in a table and used to open the spread***.
Then I select the right work*** from it and work down till I find the
data.
My work loop simply adds 1 to rNum till I lose the hyphen in position 5.

It may look crude, but it works, and you can choose which columns to move
to
which fields. (eg within an edit/addnew/update: )

![destinationfield] = ws.range("G" & rNum)


F = False

Do While F = False

F = Excel.Application.GetOpenFilename("Excel Files (*.xls), *.xls")

If F = False Then Exit Sub


Loop

Set Parms = CurrentDb.OpenRecordset("Parms")
vPW = Parms![sspassword]

Set wb = Excel.Application.Workbooks.Open(F, False, True, , vPW)
Set ws = wb.Worksheets("SheetName")
rNum = 1

Do Until InStr(1, ws.Range("A" & rNum), "-") = 5
rNum = rNum + 1

Loop









"Fletcher" wrote:

Okay, I believe that I'm in over my head on this one, and I'll do my
best to explain.

I have two databases, one in access, and one in excel. The excel
database has been used for years in my facility and is in dire nead of
replaceing. That's where my access database comes in. My boss wants
me to bring in some of the data from the old datbase into the new one
to create an overlap of data mainly to demonstrate the powers of Access
vs. Excel to some upper level management before implimenting it to the
facility. Now you're probably wondering where my problem is right?
Well here it goes:

The excel database has too much information in it to be blunt. We
would like to filter out older data and some columns. It has over 5000
rows of information in it and we would only like to import a few on
that scale. Perhaps 50 entries.

I've tried the import wizard and it won't let me import to the table
that I want and I can't ask it to filter out the first 4950 entries.

I've tried copy and paste, but that did not work at all.

I would imagine that I could delete the rows and columns in excel that
I don't want, but that would cause unheard of damage to the way the
system works now.

Can anyone help me?

Thanks,
Fletcher




.

Quantcast