excel import with missing entries

  • Thread starter Thread starter drabbacs
  • Start date Start date
D

drabbacs

I have a database which I'm using to archive a series of
weekly reports which come in excel files. I currently have
code which allows the user to select the file on the fly
and then imports via DoCmd.TransferSpreadsheet into a
temporary table. Further macros and code allow the user to
accept the imported data and update the permanent tables.

The spreadsheet requires some massaging in excel before it
can be imported. Specifically, in column A there are
blanks which I fill in with f5->special->blanks, =uparrow
ctrl-enter.

What I want to know is, can I avoid this preliminary data
massaging and do the same thing in Access? I'm using
Access2002. Example data follows.

A B...
AX237 someinfo
moreinfo
evenmoreinfo
WC77 info
moreinfo
etc etc

Thanks for any help
Drabbacs
 
Hi Drabbacs,

This isn't something that comes naturally to Access.

The first problem is that Access doesn't store records in what you might
call a sequential fashion: the rows in the spreadsheet are in a
particular order - and because of the blank cells the order is critical
to the meaning. But that order is not maintained when you import the
data to Access.

So unless there's another field or combination of fields in the data
that can be used to sort it into the same order as in the spreadsheet,
you need to "fill in the blanks" before the data has been imported,
either in Excel or during the import process.

For myself, I usually just massage it in Excel much the way you do. But
it's possible to use automation to do this from VBA code running in
Access. This might be a simpler algorithm than the one you're using:

Insert a column next to the one with the blank cells
Fill it with a formula like this (for cell B2)
=IF(ISBLANK(A2),B1,A2)
Define a named range excluding the one with the blank cells
Save the workbook
Import or link the named range to an Access table.

The alternative is to use Automation to read a row at a time from Excel
and append each as a record to the Access table, repeating the value
from the last non-blank cell in that column. This would mean working
with the worksheet's .UsedRange.Rows property and an Access recordset.

For getting-started information on automating Excel, see

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476
 
Yeah... I was afraid of that. I'll check out the sites. I
think a sequential import approach is probably the ticket.
Thanks for the advice/help, John.

Drabbacs
-----Original Message-----
Hi Drabbacs,

This isn't something that comes naturally to Access.

The first problem is that Access doesn't store records in what you might
call a sequential fashion: the rows in the spreadsheet are in a
particular order - and because of the blank cells the order is critical
to the meaning. But that order is not maintained when you import the
data to Access.

So unless there's another field or combination of fields in the data
that can be used to sort it into the same order as in the spreadsheet,
you need to "fill in the blanks" before the data has been imported,
either in Excel or during the import process.

For myself, I usually just massage it in Excel much the way you do. But
it's possible to use automation to do this from VBA code running in
Access. This might be a simpler algorithm than the one you're using:

Insert a column next to the one with the blank cells
Fill it with a formula like this (for cell B2)
=IF(ISBLANK(A2),B1,A2)
Define a named range excluding the one with the blank cells
Save the workbook
Import or link the named range to an Access table.

The alternative is to use Automation to read a row at a time from Excel
and append each as a record to the Access table, repeating the value
from the last non-blank cell in that column. This would mean working
with the worksheet's .UsedRange.Rows property and an Access recordset.

For getting-started information on automating Excel, see

Sample Excel automation
http://www.mvps.org/access/modules/mdl0006.htm

Q123859 ACC: Sample OLE Automation for MS Word and MS Excel
http://support.microsoft.com/?id=123859

ACC2000: Using Automation to Create and Manipulate an Excel Workbook
(Q210148) http://support.microsoft.com/?id=210148

ACC: Using Automation to Create and Manipulate an Excel Workbook
(Q142476)
http://support.microsoft.com/?id=142476






I have a database which I'm using to archive a series of
weekly reports which come in excel files. I currently have
code which allows the user to select the file on the fly
and then imports via DoCmd.TransferSpreadsheet into a
temporary table. Further macros and code allow the user to
accept the imported data and update the permanent tables.

The spreadsheet requires some massaging in excel before it
can be imported. Specifically, in column A there are
blanks which I fill in with f5->special->blanks, =uparrow
ctrl-enter.

What I want to know is, can I avoid this preliminary data
massaging and do the same thing in Access? I'm using
Access2002. Example data follows.

A B...
AX237 someinfo
moreinfo
evenmoreinfo
WC77 info
moreinfo
etc etc

Thanks for any help
Drabbacs
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top