Copying a date in a blank cell

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

Guest

Hi,
I am importing an excel report into access. The excel report is grouped in
to dates. This is only one date per group of data. For example:

1/1/05 data
data
data

1/2/05 data
data
data
data
I could copy and paste in excel but I have over 21,000 lines per import. Is
there a way to have access do the following:

If copy date and paste it until you get to a new date then copy and paste
until you get to a new date....

Thanks for your help.

Mike Chamis
 
The concern you must handle is that, absent any way for ACCESS to identify
the specific order in which the data rows from EXCEL are imported, you may
well lose the "order" in the table. If you import the data into a new table
and have ACCESS create a primary key for you, ACCESS will create an
autonumber field, which most likely will increment (starting at 1) for each
record imported. That will give you a reasonable sense of the order of the
records so that you could design a process to "fill in" the dates for the
records where the date is empty. Note that this is not foolproof, but has
high certainty of preserving the order.

In this case, I think I would be inclined to use VBA code to do the import
via Automation. That gives you 100% control over the process and will allow
you to write the date into the empty "field" as you do the import, thereby
eliminating the need to devise a query or queries to do the work later. The
import process will be slower this way, but you'll avoid the need for the
subsequent data updates that would be needed for the first method.

An alternative to the second method above would be to run a fairly simple
VBA macro in the EXCEL file that fills in the dates while the data are still
in EXCEL -- before you do the import. That likely will be even faster than
doing the import via Automation, and then you can import the data normally
into ACCESS.

What is your preference?
 
Ken,
Thanks for your reply.

I perfer the coding in Access. Do you thinks that would be best?

Mike
 
Hi Mike,

I'd add a column to the Excel sheet and use a formula to generate the
dates. The following assumes the dates are in column A, that row 1
contains field names and that the data starts at row 2 (so A2 contains
the first date):

1) Add a new column B.

2) In B2, type the formula
=IF(ISBLANK(A2),B1,A2)

3) Fill down to the bottom of the data.

4) Select column B and replace the formulas with the actual date values
(i.e. do Copy, then Paste Special|Values).

5) Delete column A and save.
 
Thank You John for your help. It works geat. Looks like I'll work it in
excel first than send it to Access.

Mike
 
From your reply to John Nurick's suggestion, I assume that you will not
pursue the ACCESS VBA approach. I think John's suggestion is a good one, and
simpler than my EXCEL macro one.

Post back if you still want some suggestions for ACCESS code.
 
Ken,

I'd love to see the code if you have time, but I don't want you to go to any
trouble to write it. I'd like code that will run without copying and pasting
21,000 lines. So if it's not too much trouble, I'd love to see it.

Thanks for all your help.
Mike
 
here is some generic sample code that may get you started (you can add loops
to this code to loop through each worksheet in a book if you'd like):

Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls", , True) ' opens in
read-only mode
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)
Do While xlc.Value <> ""
rst.AddNew
For lngColumn = 0 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing



For your setup, you'd need to add a few things for handling the "blank" date
cells. Assuming that the cells are in column A, this would be some generic
code for you:

Some sample code that may get you started (you can add loops to this code to
loop through each worksheet in a book if you'd like):



Dim datDateCell As Date
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xls As Object, xlc As Object
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set xlx = CreateObject("Excel.Application")
xlx.Visible = True
Set xlw = xlx.Workbooks.Open("C:\Filename.xls", , True) ' opens in
read-only mode
Set xls = xlw.Worksheets("WorksheetName")
Set xlc = xls.Range("A1")
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableName", dbOpenDynaset, dbAppendOnly)
Do While xlc.Offset(0, 1).Value <> ""
If xlc.Value <> "" Then datDateCell = xlc.Value
rst.AddNew
rst.Fields(0).Value = CDate(datDateCell)
For lngColumn = 1 To rst.Fields.Count - 1
rst.Fields(lngColumn).Value = xlc.Offset(0, lngColumn).Value
Next lngColumn
rst.Update
Set xlc = xlc.Offset(1,0)
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
Set xlc = Nothing
Set xls = Nothing
xlw.Close False
Set xlw = Nothing
xlx.Quit
Set xlx = Nothing


--

Ken Snell
<MS ACCESS MVP>
 
Back
Top