Excel Import data range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database for a client that is based on data initially retrieved from
AS400. The file is pulled into an Excel template which contains several
formulas to properly convert the data. Using a macro, certain columns are
then transferred to a spreadsheet. This spreadsheet is what I am importing
into Access. My problem is that the amount of data will vary from month to
month so my Excel template has (for example) 1000 rows which contain the
formulas. One month there may be 500 rows of data, the next month 600. Since
I have to rely on the formulas to make the correct data conversions I have to
fill in the formulas for all 1000 rows (to be sure I capture each row). So
when the transfer from the template to the spreadsheet occurs it pulls in the
rows that have no data other than the result for the formula (which shows
blanks or zeros from time conversions). When I import to Access it pulls in
all those rows. Due to the type of data I cannot set Access to 'allow zero
length' as it will error out.

This is all done in the background on the client end. How can I get the
import to not pull in those rows with no actual data?
 
Maybe the simplest thing to do would be to allow all the rows to be
imported, and then run a Delete query that deletes the ones with no
actual data.

Otherwise, since you're using an Excel macro to copy the columns you
need to a worksheet ready to import, you could modify the macro so that
it only copies the rows you actually want to import, so that the rest of
the worksheet is "virgin" and Access will not try to import it. (Or you
can get the same effect by having the macro deleting all rows of the
worksheet below the ones you need.
 
Like the delete query idea - that way the worksheets aren't disturbed. I'm
sure that will work. Thanks!
 
Back
Top