Importing to access (Excel 2003)

Topic

New Reply

Is there a way to take the attached spreadsheet and import it into access? I know I can use the File/Get external data feature but I want it to populate the table a different way. Let me explain. If I just do the File/Get- name, 3/1/07, 3/2/07,3/3/07 (dates are fields). I need it to do this: name, time, date(3/1/07) is record #1, same name, same time, next date(3/2/7) is record #2, etc.

SO for each month an employee will have 30-31 entries in my table for their work schedule.

Reply | Quote

Replies

Does the “real” worksheet look like that too, i.e. a blank row 1, the word “Month” in E2, a series of dates in row 3, a blank row 4, then a series of codes in the following rows?

Reply | Quote

The sheet actually looks like the attached.

Reply | Quote

OK, that looks good. It’s dinnertime here now, I’ll post some code later (if nobody else does so in the meantime).

Reply | Quote

Here is some code to be run from Access. It assumes

– You have either imported or linked the workbook in Access. For the sample workbook, the Access table would be named ‘Sheet 2’.

– You have created a table in Access with the correct structure. For illustration purposes, I’ll name it tblData. It has the following fields:
TheName (Text, size 25)
Position (Text, size 25)
Location (Text, size 25)
TheDate (Date/Time)
Code (Text, size 25)

– You have set a reference to the Microsoft DAO 3.6 Object Library in Tools | References in the Visual Basic Editor. (There is one by default in Access 2003).

– Here is the code:

Sub ConvertImport()
Dim dbs As DAO.Database
Dim rstIn As DAO.Recordset
Dim rstOut As DAO.Recordset
Dim i As Integer

On Error GoTo ErrHandler

Set dbs = CurrentDb
‘ Adjust the names in the following two statements
Set rstIn = dbs.OpenRecordset(“Sheet 2”, dbOpenDynaset)
Set rstOut = dbs.OpenRecordset(“tblData”, dbOpenDynaset)
Do While Not rstIn.EOF
For i = 3 To rstIn.Fields.Count – 1
If Not rstIn.Fields(i) & “” = “” Then
rstOut.AddNew
rstOut.Fields(0) = rstIn.Fields(0)
rstOut.Fields(1) = rstIn.Fields(1)
rstOut.Fields(2) = rstIn.Fields(2)
rstOut.Fields(3) = CDate(rstIn.Fields(i).Name)
rstOut.Fields(4) = rstIn.Fields(i)
rstOut.Update
End If
Next i
rstIn.MoveNext
Loop

ExitHandler:
On Error Resume Next
rstOut.Close
Set rstOut = Nothing
rstIn.Close
Set rstIn = Nothing
Set dbs = Nothing
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

– As is, this code will only create a record if something has been filled in in the spreadsheet for that date.

– If you want a record for each employee / each date, remove or comment out the If … Then and End If lines.

Reply | Quote

Let me ask you this question.
If I create a worksheet for each unit (people working in a different location) can I use the same code to pull each sheet over? Also, when does the code execute? Here is my thinking: the user enters all the information into excel. She goes to access to print a schedule – all the information is pulled in since I will link the workbook to access.

Thanks so much for your help.

Reply | Quote

You can create a link to each different sheet.
You can use a variable instead of “Sheet 2” in the code, and set the variable before executing ConvertImport.
You’ll have to call ConvertImport explicitly at some point.

PS Why not print the schedule from Excel?

Reply | Quote

The process is working great. The only problem is that it is not picking up the first date entry. I copied your code and made some moficiations for specs.

Thanks, I appreciate your help!

Reply | Quote

The loop that reads the date entries is

For i = 3 To rstIn.Fields.Count – 1
…
Next i

This starts at column D (fields are numbered starting at 0, so 3 is actually the 4th field), based on the spreadsheet you attached. You’ll have to adjust it if the dates start in another column.

Reply | Quote

It has been awhile since I used the code or even looked at the access database. I am now ready to tackle it.

When I link to excel, my dates (which are my headings) change to F2, F3, F4 so the code is giving me a type mismatch. I’m not sure what I am doing incorrectly. Can you shed some light? I know it worked before and all I did was change the date headings for another month.

Reply | Quote

Did you make sure to tick the check box “First Row Contains Column Headings”?

Reply | Quote

Yes. The spreadsheet looks perfect and even in the import process I can see the dates but when I open the linked table, I get F2, F3, F4 etc.

Reply | Quote

Could you post a small sample workbook (with dummy data) that exhibits this behavior when linked in Access?

Reply | Quote

Here is a copy of the linked table.

Reply | Quote

I could not figure out how to attach more than one file. Here is a copy of the excel sheet.

Reply | Quote

The way you did it is fine, but in the future, you can create a zip file containing multiple files and attach the zip file (as long as it is below the limit of 100 KB).

I cannot explain why it worked with the original dates but not with the new ones, but you can work around it by inserting an apostrophe ' in front of the dates in row one. This makes Excel interpret them as text values.

Reply | Quote

Is there an easy way of entering the dates as text? I have to do this every month to enter the schedule information. My goal would be to create a macro that would allow me to select the month I want to populate the dates for and then all I do is enter the work schedule.

Reply | Quote

The following macro will prompt you for a date, then enter the dates in the month as text in B1, C1, … as text values:

Sub FillMonth()
Dim strInput As String
Dim datInput As Date
Dim datStart As Date
Dim datEnd As Date
Dim d As Date
Dim c As Long
strInput = InputBox("Enter first of month")
If Not IsDate(strInput) Then
MsgBox "Invalid input.", vbExclamation
Exit Sub
End If
datInput = DateValue(strInput)
datStart = datInput - Day(datInput) + 1
datEnd = DateAdd("m", 1, datStart) - 1
Range("B1:IV1").ClearContents
c = 2
For d = datStart To datEnd
Cells(1, c) = Chr(39) & d
c = c + 1
Next d
End Sub

Reply | Quote

Hans this is great and I appreciate your help. I do have one more question. When I run the routine, it works greatly and then when I go to access the process also work well. There is one more problem I need your advice on. If I go from a 31 month to a 30 day month, I get a field with the heading f32. So the macro in access is stopping because there is no date 31. How do I correct this?

Reply | Quote

Try this:

For i = 2 To rstIn.Fields.Count – 1
If IsDate(rstIn.Fields(i).Name) Then
If Not rstIn.Fields(i) & “” = “” Then
rstOut.AddNew
rstOut.Fields(0) = rstIn.Fields(0)
rstOut.Fields(1) = rstIn.Fields(1)
rstOut.Fields(2) = rstIn.Fields(2)
rstOut.Fields(3) = CDate(rstIn.Fields(i).Name)
rstOut.Fields(4) = rstIn.Fields(i)
rstOut.Update
End If
End If
Next i

The added lines (in bold) check whether the field name is a valid date.

Reply | Quote

Ok. I entered some data and linked the spreadsheet to access. I ran the code and I get an error message Numeric field overflow. I checked the imported table and I have #num! in some of the fields. The fields should all be text not number since the time range is 6a-6p or 3a. What could be the problem?

I selected all the data and formatted it to text in excel but I still have some importing as numbers and other as text.

Reply | Quote

Can you attach a small sample worksheet that demonstrates the problem?

Reply | Quote

I figured it out. For some reason it did not like the selecting and formatting as text. I went through and click on each cell and press ‘f2’ and the data linked correctly.

Thanks for your offer to help!

Reply | Quote