|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
First order of business. Excel is not a database.
If you plan to have one flat table, like the spreadsheet in Excel, in the
Access database, you need to talk to someone with experience in designing
relational databases; otherwise, you would be just as well off staying with
Excel.
Now, if you just want a subset of the data from the Excel spreadsheet, you
can Link the spreadsheet as if it were a table and use append queries to
populate your Access tables.
Seriously, do review your data structure. Get a book on database
normalization, and/or ask some questions in the news groups. If you are
coming from an Excel background and have not developed a relation database
before, there are a lot of traps to avoid and some good things to learn so
you don't make things harder than they need to be.
Good Luck with it.
"Fletcher" wrote:
[Quoted Text] > 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
>
>
|
|
|
[Quoted Text] > First order of business. Excel is not a database.
Right, we absolutely know this. That is why we're trying to get away
from it and to Access. We've been having trouble with the operators
getting into the script for our macros and causing hell.
> If you plan to have one flat table, like the spreadsheet in Excel, in the
> Access database, you need to talk to someone with experience in designing
> relational databases; otherwise, you would be just as well off staying with
> Excel.
We don't really want to create any relationships. We are building this
database simply to collect data and link up with an outside Statistical
Process Control (SPC) application. There is no real reason to build a
complex database for this. We only want a table for each machine in
it. I've heard that this is not the best type of database to build,
but we REALLY have no reason to do anything different.
> Now, if you just want a subset of the data from the Excel spreadsheet, you
> can Link the spreadsheet as if it were a table and use append queries to
> populate your Access tables.
All we really want to do is bring in some of the recent data to be
viewed with this new SPC application with the link through access and
show management how it works before implimentation. And since we're
building this new data collection database we have decided that we've
been collecting some irrelevant data and would like to filter it out.
I was wondering if there was a way to do this without copying and
pasting the desired data into a new spreadsheet, then importing (which
is what I'm doing now). It works, but I was hoping for an easier, more
automated way.
> Good Luck with it.
Thank you.
|
|
Understand. I have only a few comments.
First, If you have had problems with users messing with the code in Excel,
then unless you implement security or at least deliver only an MDE file, they
will still be able to do that.
Why do you need a different table for each machine? If there will be
differences in the field among the tables, then it is reasonable. If all
data elements are the same for all machines, then add a field that identifies
the machine and use one table.
Since you have multiple operators, it will be important that you split your
database, put the back end on a share folder that all operators have
permissions to and install a copy of the front end on each operators computer.
As to getting the data from Excel, If you don't want to use queries to
manipulate the data, then cut and paste will probably do.
"Fletcher" wrote:
[Quoted Text] > > First order of business. Excel is not a database.
>
> Right, we absolutely know this. That is why we're trying to get away
> from it and to Access. We've been having trouble with the operators
> getting into the script for our macros and causing hell.
>
> > If you plan to have one flat table, like the spreadsheet in Excel, in the
> > Access database, you need to talk to someone with experience in designing
> > relational databases; otherwise, you would be just as well off staying with
> > Excel.
>
> We don't really want to create any relationships. We are building this
> database simply to collect data and link up with an outside Statistical
> Process Control (SPC) application. There is no real reason to build a
> complex database for this. We only want a table for each machine in
> it. I've heard that this is not the best type of database to build,
> but we REALLY have no reason to do anything different.
>
> > Now, if you just want a subset of the data from the Excel spreadsheet, you
> > can Link the spreadsheet as if it were a table and use append queries to
> > populate your Access tables.
>
> All we really want to do is bring in some of the recent data to be
> viewed with this new SPC application with the link through access and
> show management how it works before implimentation. And since we're
> building this new data collection database we have decided that we've
> been collecting some irrelevant data and would like to filter it out.
> I was wondering if there was a way to do this without copying and
> pasting the desired data into a new spreadsheet, then importing (which
> is what I'm doing now). It works, but I was hoping for an easier, more
> automated way.
>
> > Good Luck with it.
>
> Thank you.
>
>
|
|
|
[Quoted Text] > First, If you have had problems with users messing with the code in Excel,
> then unless you implement security or at least deliver only an MDE file, they
> will still be able to do that.
The security that we are putting in place for this database is having
the forms that take up the whole screen and allowing only the engineers
to get into the system. It's not that the operators would purposfully
mess up the code, it is just that they would accidentally get into the
system and delete a cell here or hit a button there and it could be
detrimental to the way the whole thing worked.
> Why do you need a different table for each machine? If there will be
> differences in the field among the tables, then it is reasonable. If all
> data elements are the same for all machines, then add a field that identifies
> the machine and use one table.
You are the first to respond in this way. Thank you. We have
different data for each machine so a single table would be unreasonably
difficult. Everyone else who responded to a message asking for help
told me that I was going about it wrong not using relationships and the
like. I hope you respond to more of my posts.
I have done as indicated with a field identifying different machines in
tables where I can log machines that have similiar data associated with
each.
> Since you have multiple operators, it will be important that you split your
> database, put the back end on a share folder that all operators have
> permissions to and install a copy of the front end on each operators computer.
I'm not familiar with this idea of the front end and back end, but I
don't believe that it will be an issue for our company. I don't know
if you are familiar with a citrix environment, but that is what our
company uses for its networking. Essentially it replaces computers for
each users with what we call "ding-boxes." Ding-boxes are essentially
very small computers without any drives. They have ports in the back
for monitor, mouse, keyboard, power and network cable. Users log
directly into a server from these using a name and password. This has
been especially useful in a production facility where space is minimal.
So what we have to do is publish our database onto the servers in
shared folders (as you indicated) that the operators can access if
their user name is given permission.
> As to getting the data from Excel, If you don't want to use queries to
> manipulate the data, then cut and paste will probably do.
I have tried cutting and pasting from excel to Access and access gives
me the error that the data is too long. So what I've been doing is
going through the current spreadsheets and cutting chunks of the
desired data and pasting it to a new sheet, then importing it to
access. This works, but is alot of work for little output.
Thank you again for your help.
|
|
See comments below:
"Fletcher" wrote:
[Quoted Text] > > First, If you have had problems with users messing with the code in Excel,
> > then unless you implement security or at least deliver only an MDE file, they
> > will still be able to do that.
>
> The security that we are putting in place for this database is having
> the forms that take up the whole screen and allowing only the engineers
> to get into the system. It's not that the operators would purposfully
> mess up the code, it is just that they would accidentally get into the
> system and delete a cell here or hit a button there and it could be
> detrimental to the way the whole thing worked.
>
> > Why do you need a different table for each machine? If there will be
> > differences in the field among the tables, then it is reasonable. If all
> > data elements are the same for all machines, then add a field that identifies
> > the machine and use one table.
>
> You are the first to respond in this way. Thank you. We have
> different data for each machine so a single table would be unreasonably
> difficult. Everyone else who responded to a message asking for help
> told me that I was going about it wrong not using relationships and the
> like. I hope you respond to more of my posts.
>
> I have done as indicated with a field identifying different machines in
> tables where I can log machines that have similiar data associated with
> each.
>
> > Since you have multiple operators, it will be important that you split your
> > database, put the back end on a share folder that all operators have
> > permissions to and install a copy of the front end on each operators computer.
>
> I'm not familiar with this idea of the front end and back end, but I
> don't believe that it will be an issue for our company. I don't know
> if you are familiar with a citrix environment, but that is what our
> company uses for its networking. Essentially it replaces computers for
> each users with what we call "ding-boxes." Ding-boxes are essentially
> very small computers without any drives. They have ports in the back
> for monitor, mouse, keyboard, power and network cable. Users log
> directly into a server from these using a name and password. This has
> been especially useful in a production facility where space is minimal.
>
> So what we have to do is publish our database onto the servers in
> shared folders (as you indicated) that the operators can access if
> their user name is given permission.
Splitting the database is very important in an environment like yours. I
would suggest you do some research. Basically, what happens in the split
process (Tools, Database Utilities, Database Splitter) is that all tables are
put into a different mdb with _be added to the name. For example if your mdb
is named Foobah.mdb, it will create two mdbs. Foobah_be.mdb which will
contain all your tables. Foobah.mdb will contain all other objects (forms,
reports, queries, etc). It will automatically link Foobah to the tables in
Foobah_be.
This may seem to be overkill, but it is not. It will make your life easier
for development. With a non split mdb, every time you need to add
functionality or correct an error, you have to get everybody out of the
database and move all your objects to the production database before it can
be used again. With a split database, all changes are made to a copy of the
front end. Once that is done, you just copy the new front end over the old
one.
>
> > As to getting the data from Excel, If you don't want to use queries to
> > manipulate the data, then cut and paste will probably do.
>
> I have tried cutting and pasting from excel to Access and access gives
> me the error that the data is too long. So what I've been doing is
> going through the current spreadsheets and cutting chunks of the
> desired data and pasting it to a new sheet, then importing it to
> access. This works, but is alot of work for little output.
The problem you are having is that Access will accept a maximum of 255
characters in a text field, but Excel does not have that restraint.
Post back if you have more questions as you move forward.
>
> Thank you again for your help.
>
>
|
|
Thanks for your help. I guess I'll have to do it the way that I'm
doing it now. I'll look into splitting the database as you said.
Thanks for the advice as well. I'll let you know how everything turns
out if possible.
Klatuu wrote:
[Quoted Text] > See comments below:
>
> "Fletcher" wrote:
>
> > > First, If you have had problems with users messing with the code in Excel,
> > > then unless you implement security or at least deliver only an MDE file, they
> > > will still be able to do that.
> >
> > The security that we are putting in place for this database is having
> > the forms that take up the whole screen and allowing only the engineers
> > to get into the system. It's not that the operators would purposfully
> > mess up the code, it is just that they would accidentally get into the
> > system and delete a cell here or hit a button there and it could be
> > detrimental to the way the whole thing worked.
> >
> > > Why do you need a different table for each machine? If there will be
> > > differences in the field among the tables, then it is reasonable. If all
> > > data elements are the same for all machines, then add a field that identifies
> > > the machine and use one table.
> >
> > You are the first to respond in this way. Thank you. We have
> > different data for each machine so a single table would be unreasonably
> > difficult. Everyone else who responded to a message asking for help
> > told me that I was going about it wrong not using relationships and the
> > like. I hope you respond to more of my posts.
> >
> > I have done as indicated with a field identifying different machines in
> > tables where I can log machines that have similiar data associated with
> > each.
> >
> > > Since you have multiple operators, it will be important that you split your
> > > database, put the back end on a share folder that all operators have
> > > permissions to and install a copy of the front end on each operators computer.
> >
> > I'm not familiar with this idea of the front end and back end, but I
> > don't believe that it will be an issue for our company. I don't know
> > if you are familiar with a citrix environment, but that is what our
> > company uses for its networking. Essentially it replaces computers for
> > each users with what we call "ding-boxes." Ding-boxes are essentially
> > very small computers without any drives. They have ports in the back
> > for monitor, mouse, keyboard, power and network cable. Users log
> > directly into a server from these using a name and password. This has
> > been especially useful in a production facility where space is minimal.
> >
> > So what we have to do is publish our database onto the servers in
> > shared folders (as you indicated) that the operators can access if
> > their user name is given permission.
>
> Splitting the database is very important in an environment like yours. I
> would suggest you do some research. Basically, what happens in the split
> process (Tools, Database Utilities, Database Splitter) is that all tables are
> put into a different mdb with _be added to the name. For example if your mdb
> is named Foobah.mdb, it will create two mdbs. Foobah_be.mdb which will
> contain all your tables. Foobah.mdb will contain all other objects (forms,
> reports, queries, etc). It will automatically link Foobah to the tables in
> Foobah_be.
>
> This may seem to be overkill, but it is not. It will make your life easier
> for development. With a non split mdb, every time you need to add
> functionality or correct an error, you have to get everybody out of the
> database and move all your objects to the production database before it can
> be used again. With a split database, all changes are made to a copy of the
> front end. Once that is done, you just copy the new front end over the old
> one.
> >
> > > As to getting the data from Excel, If you don't want to use queries to
> > > manipulate the data, then cut and paste will probably do.
> >
> > I have tried cutting and pasting from excel to Access and access gives
> > me the error that the data is too long. So what I've been doing is
> > going through the current spreadsheets and cutting chunks of the
> > desired data and pasting it to a new sheet, then importing it to
> > access. This works, but is alot of work for little output.
>
> The problem you are having is that Access will accept a maximum of 255
> characters in a text field, but Excel does not have that restraint.
>
> Post back if you have more questions as you move forward.
>
> >
> > Thank you again for your help.
> >
> >
|
|
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 spreadsheet with
excel.application.workbooks.open("your file")
You can navigate round the sheet 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 spreadsheet, 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 spreadsheet 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 spreadsheet.
The password is stored in a table and used to open the spreadsheet.
Then I select the right worksheet 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:
[Quoted Text] > 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
>
>
|
|
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 spreadsheet, 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:
[Quoted Text] > 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 spreadsheet with
> excel.application.workbooks.open("your file")
>
> You can navigate round the sheet 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 spreadsheet, 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 spreadsheet 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 spreadsheet.
> The password is stored in a table and used to open the spreadsheet.
> Then I select the right worksheet 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
> >
> >
|
|
Well I'm doing okay with my importing of data (I'm using the wizard).
Except when I try to do some of the tables I get this error:
Method 'Columns' of object 'IImexGrid' failed
I have no idea what it means. If I click ok on that error and go on to
click the finish button it tells me that it can't import because of an
error. No idea what's going on.
|
|
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[ at ]discussions.microsoft.com> wrote in message
news:B90DD299-BB05-4FBD-B0CD-2B91F03B3504[ at ]microsoft.com...
[Quoted Text] > 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 spreadsheet, 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 spreadsheet with
>> excel.application.workbooks.open("your file")
>>
>> You can navigate round the sheet 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 spreadsheet, 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 spreadsheet 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 spreadsheet.
>> The password is stored in a table and used to open the spreadsheet.
>> Then I select the right worksheet 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
>> >
>> >
|
|
I don't see why 63 + null can't be 63 like it always used to be. (And indeed
like another MVP assured me it still was not long ago)
And as for having to pass every database field through NZ in coding. In the
words of the tennis player, you cannot be serious.
"Douglas J. Steele" wrote:
[Quoted Text] > 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[ at ]discussions.microsoft.com> wrote in message > news:B90DD299-BB05-4FBD-B0CD-2B91F03B3504[ at ]microsoft.com... > > 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 spreadsheet, 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 spreadsheet with > >> excel.application.workbooks.open("your file") > >> > >> You can navigate round the sheet 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 spreadsheet, 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 spreadsheet 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 spreadsheet. > >> The password is stored in a table and used to open the spreadsheet. > >> Then I select the right worksheet 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 > >> > > >> > > > > |
|
|