ACCESS FEED: Re: [MS_AccessPros] Re: Import Excel Spreadsheet into Access 2003 table

Rabu, 30 Januari 2013

Re: [MS_AccessPros] Re: Import Excel Spreadsheet into Access 2003 table

Another possibility could be to scrub or validate the data prior to adding it to a table. Here's an example of some DAO code that adds rows to an Access table from Excel.

Sub ProcessCOS_Report()

' Import COS Report data into access table
' Connect to the database

Dim strSports As String
Dim strArthroplasty As String
Dim strFracture As String
Dim strDiagnosis As String

Dim newCount As Long
Dim i As Long
Dim cn As New ADODB.Connection
Dim rs As Recordset

Dim sh As Worksheet
Dim myData As String
Set sh = ActiveWorkbook.ActiveSheet
myData = ThisWorkbook.Path & "\WNH_III_Operative_Log_New_JJ.mdb"

With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Data Source") = myData
.Open
End With

Set rs = New ADODB.Recordset

'open record
rs.Open "select * from [TestCaseLogs];", cn, adOpenKeyset, adLockOptimistic

COS_Rpt.Activate

'get record count in excel
newCount = Cells.Range("A65536").End(xlUp).Row

If RowCount + 2 > newCount Then
MsgBox "No data to be append to Access!"
End If

LastRow = ActiveSheet.UsedRange.Rows.Count
For i = 2 To LastRow ' start from row 2 in the worksheet
If Not IsEmpty(Range("R" & i).Value) Then
strDiagnosis = Range("R" & i).Value
If Not IsEmpty(Range("S" & i).Value) Then
strDiagnosis = strDiagnosis & ", " & Range("S" & i).Value
If Not IsEmpty(Range("T" & i).Value) Then
strDiagnosis = strDiagnosis & ", " & Range("T" & i).Value
If Not IsEmpty(Range("U" & i).Value) Then
strDiagnosis = strDiagnosis & ", " & Range("U" & i).Value
If Not IsEmpty(Range("V" & i).Value) Then
strDiagnosis = strDiagnosis & ", " & Range("V" & i).Value
End If
End If
End If
End If
End If

'INSERT INTO [Case Log Fields] ( [Case Number], [Patient Name], [Medical Record Number], [Date of Surgery], Diagnosis, [CPT Code], [Procedure Description], [Secondary CPT Code], [Secondary Procedure], CPT_Code_Three, Procedure_Description_Three, CPT_Code_Four, Procedure_Description_Four, [Sports Medicine Case], Fracture_Case, Arthroplasty_Case )
'SELECT [Case Log Fields].[Case Number], [Case Log Fields].[Patient Name], [Case Log Fields].[Medical Record Number], [Case Log Fields].[Date of Surgery], [Case Log Fields].Diagnosis, [Case Log Fields].[CPT Code], [Case Log Fields].[Procedure Description], [Case Log Fields].[Secondary CPT Code], [Case Log Fields].[Secondary Procedure], [Case Log Fields].CPT_Code_Three, [Case Log Fields].Procedure_Description_Three, [Case Log Fields].CPT_Code_Four, [Case Log Fields].Procedure_Description_Four, [Case Log Fields].[Sports Medicine Case], [Case Log Fields].Fracture_Case, [Case Log Fields].Arthroplasty_Case
'FROM [Case Log Fields];

strSports = "True"
strArthroplasty = "False"
strFracture = "True"

With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Medical Record Number") = Range("A" & i).Value
.Fields("Patient Name") = Range("B" & i).Value & ", " & Range("C" & i).Value
.Fields("Date of Surgery") = Range("G" & i).Value
.Fields("CPT Code") = Range("H" & i).Value
.Fields("Secondary CPT Code") = Range("I" & i).Value
.Fields("CPT_Code_Three") = Range("J" & i).Value
.Fields("CPT_Code_Four") = Range("K" & i).Value
.Fields("Diagnosis") = strDiagnosis
.Fields("Sports Medicine Case") = strSports
.Fields("Fracture_Case") = strArthroplasty
.Fields("Arthroplasty_Case") = strFracture
.Update ' stores the new record in the input_com_Copy table
End With
Next i
DoEvents

' close the record set and connection
rs.Close
Set rs = Nothing
Set dbs = Nothing
cn.Close
Set cn = Nothing

Exit Sub

HandleError:
Stop
' MsgBox "Error Number: " & Err & " -- " & "Error: " & Error & " -- " & "Error Line: " & Erl
'MsgBox "Error Number: " & Err & " -- Error: " & Error & " -- Error Line: " & Erl & " -- Procedure: " & Application.VBE.ActiveCodePane.CodeModule & " -- Procedure: " & C_PROC_NAME
'MsgBox Err & " " & Error
Debug.Print "MsgBox Err: " & Err & " Error: " & Error

Resume 'Next
End Sub ' end ProcessCOS_Report

Thus endth my 02 cents,
Jeff
----- Original Message -----

John-

An alternative would be to build an existing table with the correct field
names, but all the data types set to Text. Import the data into that, then
run code on the imported data that examines the data row by row and corrects
it. You could even spit it out into another table with correct data types
after massaging it.

I'm not much of an expert on Excel, but it seems to me you could add code to
the spreadsheet to validate the data as it's entered. Don't allow anything
but a date in a date column, for example.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto: MS_Access_Professionals@yahoogroups.com ] On Behalf Of dnwinberry
Sent: Wednesday, January 30, 2013 3:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Import Excel Spreadsheet into Access 2003 table

John,

I hope others chime in on this, but my idea would be to send the vendors a
locked down database that they can update that contains a procedure to
create the spreadsheet after they make the updates and emails it to your
customer. You could use the free Access runtime so the vendors wouldn't have
to have Access installed on their PC's.

Doyce

--- In MS_Access_Professionals@yahoogroups.com , "jfakes.rm" wrote:
>
> I have a customer that sends an Excel spreadsheet to different vendors for
employee updates. I developed a spreadsheet template that is used to send
to the vendor.
>
> The vendors are supposed to enter their data into the template using the
specified data types. Then, my customer imports the data into a temp table
then using a process I developed (using a form), the customer runs a series
of steps to clean the data then add the data to the "main table."
>
> The problem is, the vendors are rearranging the layout of the template,
entering text in date fields etc. So, when my customer wants to import the
data into the temp table they get errors (sent to paste errors table). They
then say my process doesn't work. When I sit with them and troubleshoot the
issues on the spreadsheet, they exclaim that the automated process takes
almost as much time to complete as it would to manually enter the data. I
agreed that might be true on small vendors, but when you have to update 500+
records, that would take a lot of time.
>
> Can anyone point me to a good source to learn how to automatically
troubleshoot errors on the spreadsheet (i.e. ignore text in date fields
etc.)? I told my customer to lock the formats on the spreadsheet however,
the vendors continually make changes anyway.
>
> Any help would be appreciated.
>
> John F.
>

------------------------------------

Yahoo! Groups Links

--

Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com

919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net

[Non-text portions of this message have been removed]