Hi there,
What you want to do is actually fairly advanced work; I just did
some of it myself and I'm reasonably good with both programs.
If I might suggest you consider getting a copy of
Access Database Design & Programming (3rd Edition)
Steven Roman
and
Microsoft Excel and Access Integration: With Microsoft Office 2007
by Michael Alexander After you've developed a little skill
with MS Access.
I expect you will find you can link to your Excel worksheets and use
them directly; If they are set up in a flat data format. Vs a
'Report format' which is more organized for presentation on the
excel page.
The top row of the Excel worksheet should be column names, if you've
used multiple rows for column names to accommodate formatting, it
will mess with Access.
2 All the Data in any given column should to be the same type.
If you've mixed numbers, text, dates; it will cause errors in the
linked table.
I have had to work with these issues myself, and I have found the
most efficient way for me to pull Excel Data into Access is this
manner.
1. I create a 'linked table' in access to a given Excel worksheet.
2. I make of copy of that linked 'Table' as a local table in
Access copying the structure. This is much easier than copying
structure by hand.
3. I change the format of Any mixed rows to text. My experience
has been few actual excel spreadsheets I have seen are really
disciplined about not mixing data types in columns.
4. Though a command button I call the DPurge and DPull Routines you
see below, this is not all that complex a bit of code actually.
The Command Button Code
Loads certain variables that will be needed by DPurge and DPull
DPurge Clears the Table in question of any existing Data.
DPull Pulls a block of Text from the worksheet called.
In a little more Detail the command button loads a set of variables
ie
the name of,
the workbook, StrDocName,
the Worksheet, strWrkSheet
the Path to the Document, strpath
the first row and last column of the block of data
I want to pull. iFirstRow, iLastColumn
and the end of block marker, usually *69
And then calls DPurge
the DPurge routine:
basically just calls a Delete Query for the table in question that
clears the current contents of the table prior to
the pull. Of course you have to create a delete query for each
table, but thats not hard really. Just create a standard query
for that table rename it according the to the convention.
purge-MyTable,
and in design view replace the SQL with something like this;
DELETE [MyTable].*
FROM [MyTable];
Finally the DPull Routine
Populates the Access Table with Data from a Block of Cells
in the Excel workbook, while you could use a named range, you would
have to name that range for each workbook, this was just easier for
me.
Please note the DoEvents command in the main loop
this is very important; without it the VBA will lockout
your computer until the pull is completed.
I use a pair of Labels, lblMsgBox, lblshowme to create a virtual
window during the pull this display looks like an old school green
on black screen. It allows me to keep track of the progress of the
pull and verify the app has not stalled - you can without harm
delete that code from the block below, but me, I like visual
indications of progress, at least till I know something is working.
Although you probably could not just past the code below
into your access DBase and just use it; I expect you could
adapt it over with a minimal difficulty. I have used it to
pull data from several Workbooks into a common environment, and
I find it works well for Datablocks under 1000 Rows, after which it
gets too slow; but I suspect your not working with huge chunks of
Data.
--------------------------------------------------
Private Sub cmdShiftReportData_Click()
' Parameters iFirstRow, iLastColumn, strEnd, _
' strPath, strDocName, strTableName, strWrkSheet
Dim iFirstRow As Integer, iLastColumn As Integer, strEnd As String
Dim strPath As String, strDocName As String, strTableName As String
iFirstRow = 2: iLastColumn = 64: strEnd = "*69": strWrkSheet =
"Data"
Let strPath = "E:\!VBA\Access-MapTool\"
Let strDocName = "DCI-ReportDB.xls"
Let strTableName = "ShiftReportData"
Call DPurge(strTableName)
Call DPull(iFirstRow, iLastColumn, strEnd, strPath, strDocName,
strWrkSheet, strTableName)
End Sub
Public Sub DPurge(strTableName)
'Purge Data in the table Prior to Loading New Data
DoCmd.SetWarnings False
stDocName = "Purge-" & strTableName
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True
' Records now Purged
End Sub
Public Sub DPull(iFirstRow, iLastColumn, strEnd, strPath,
strDocName, strWrkSheet, strTableName)
Dim DRS As DAO.Recordset
Dim db As Database
Dim wb As Workbook, vTest As Variant, R As Integer, C As Integer, RC
As Long
Dim i As Integer, iEnd As Integer, MySQL$, strCap As String, StrVal
As String
'Open Record Set
'and Establish path to the workbook.
Set db = CurrentDb
Set DRS = db.OpenRecordset(strTableName, dbOpenDynaset)
Set wb = Workbooks.Open(strPath & strDocName, True, True)
'Open Vitual Green/Black Screen.
' MsgBox ("Connection Made")
Let lblMsgbox.BackColor = 0
Let lblMsgbox.ForeColor = 65380
Let lblMsgbox.Caption = "Connection Made"
Call Pauseapp
Let lblMsgbox.Caption = strWrkSheet
Call Pauseapp
Let lblShowMe.BackColor = 0
Let lblShowMe.ForeColor = 65380
R = iFirstRow - 1
'**********************
Let RC = DRS.RecordCount
If RC > 1 Then DRS.MoveLast
Do
Let R = R + 1
For C = 1 To iLastColumn
vTest = wb.Worksheets(strWrkSheet).Cells(R, C).Text
If C = 1 Then
If DRS.EOF = True Then
DRS.AddNew
Else
' DRS.Edit
End If: End If
Let strCap = vTest
Let lblShowMe.Caption = "R=" & R & Chr(13) & Chr(10) & strCap
i = C - 1
If strCap = strEnd Then iEnd = 1
'If R > 274 Then iEnd = 1
If strCap = "" Then strCap = "00"
On Error GoTo Err_DataHandlingError
Let DRS.Fields(i).Value = strCap
On Error GoTo Err_cmdPullData_Click
DoEvents
Next C:
DRS.Update , False
Loop Until iEnd = 1
' Pull Completed
' *********************************************
'Blackout Progress Screen
Let lblShowMe.BackColor = -2147483633
Let lblShowMe.ForeColor = -2147483633
'UpDate Status Screen
Let lblMsgbox.BackColor = 0
Let lblMsgbox.ForeColor = 65380
Let lblMsgbox.Caption = "UpLoad Completed"
Call QPauseapp
DoCmd.SetWarnings False
wb.Close False
DRS.Close
db.Close
DoCmd.SetWarnings True
Set wb = Nothing
Set DRS = Nothing
Set db = Nothing
'Update Status Screen ("Connections Closed")
Let lblMsgbox.BackColor = 0
Let lblMsgbox.ForeColor = 65380
Let lblMsgbox.Caption = "Connection Closed"
Call QPauseapp
Let lblMsgbox.BackColor = -2147483633
Let lblMsgbox.ForeColor = -2147483633
Exit_cmdPullData_Click:
Exit Sub
Err_cmdPullData_Click:
MsgBox ("R=" & R & " C=" & C & " TXT=" & strCap & " ") & "ERR= " &
Err.Description
E$ = "Error"
MsgBox Err.Description
Resume Exit_cmdPullData_Click
Err_DataHandlingError:
Let strCap = "000"
Let DRS.Fields(i).Value = strCap
Resume
End Sub
