Gang,
I use the current code to insert data from my workbook in to a table in my SQL2000 database:
Private Sub CommandButton1_Click()
'If template is contained in the workbook name then refuse to upload
If InStr(LCase(ActiveWorkbook.Name), "template") <> 0 Then
MsgBox "Please save the workbook before attempting to upload the results."
Exit Sub
End If
'If saved file name is not at bottom of summary sheet don't allow upload
If InStr(LCase(Sheets("Summary").Range("A47")), "template") <> 0 Then
MsgBox "Please verify that the saved file name is at the bottom of the summary sheet." & vbCrLf & _
"If the saved file name is not there, please save the file again."
Exit Sub
End If
'Verify compile has been done…
If Sheets("Summary").Range("B3") = "" Then
MsgBox "It doesn’t look like you have compiled this workbook." & vbCrLf & _
"Please make sure the workbook has been compiled before uploading results."
Exit Sub
End If
'Verify Purpose ID…
If Sheets("Conditions").Range("B1") = "" Then
MsgBox "It doesn’t look like you have entered a Purpose for this EF." & vbCrLf & _
"Please make sure the Purpose has been filled in before uploading results."
Exit Sub
End If
'Verify Project ID…
If Sheets("Conditions").Range("B2") = "" Then
MsgBox "It doesn’t look like you have entered the Project ID for this EF." & vbCrLf & _
"Please make sure the Project ID has been filled in before uploading results."
Exit Sub
End If
'If it made it here then everything must be OK so export the data
Dim con As Object
Set con = CreateObject("ADODB.Connection")
con.Open _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & _
"Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
con.Execute _
"INSERT INTO [ODBC;Driver={SQL Server};" & _
"SERVER=d1cply;DATABASE=d1cply;" & _
"UID=******;Pwd=*******;].MeasurementResults (Table_Column_Names)" & _
" SELECT Spreadsheet_Column_Names FROM [SQL_Dump$];"
con.Close
End Sub
In it's current form, if the user of the spreadsheet keeps hitting the upload button, I will get as many inserts in to my database as they hit the button.
Is it possible to have the following happen:
1. User hits upload button
2. Excel queries DBase to see if entry already exists by checking date/time stamp that is uploaded initially by Excel.
3. If no database entry has matcyhing date/time stamp then upload is performed.
4. If there is a database entry with a matching date/time stamp then user is asked if they would like to update previous entry
5. The user can then update previously uploaded entry or cancel out
In my experience, however limited it maybe, nothing is impossible when it comes to programing. So I assume this is possible, I just don't currently have the knowledge to make it happen.
Thanks for your time,
JB
Bookmarks