The following code is simply pasted in from a Microsoft Access VBA Module. It imports a production schedule spreadsheet and puts the results in a SQL Server table. The point of using this code is first to reduce the user interface to a 'click this button and it's done' simplicity, and second to edit for possible problems. One of the frustrations I run into periodically is having to bounce around all kinds of code snippets when what I would like to see is one completely coded routine that solves a problem, and I can put all the snippets in context. That's the point of publishing this routine: it's real code and runs end-to-end. The file name of the spreadsheet is passed to the routine through pubSpreadsheetName. The stored procecure 'ImportScheduledRepack' simply inserts the record if it doesn't already exist. There are leading blanks in the code that are not visible while the code is viewed as HTML. To view the code with the indentations click on 'View Source' in your browser, select and Ctrl-'C'opy the program code. Open Notepad and Ctrl-V(Paste) the code into Notepad, then 'Replace-All' <BR>s with single-spaces.

Public oExcel As Object Public xla As Excel.Application Public pubSpreadsheetName As String Sub ImportProductionScheduleSpreadsheet() Dim SQLString As String Dim CellText As String Dim ReportDate As String Dim PriorSchedule As String Dim Schedule As String Dim Needed As String Dim RepackNumber As String Dim EstimatedHours As String Dim Warehouse As String Dim ProductCode As String Dim Description As String Dim Label As String Dim SizeCode As String Dim Cases As String Dim Pounds As String Dim Comments As String Dim BuyerCode As String Dim InternalUPC As String Dim ExternalUPC As String Dim RowNumber As Integer ' ImportProductionScheduleSpreadsheet Dim NullCell As Integer Set oExcel = CreateObject("Excel.Application") oExcel.Visible = True oExcel.UserControl = True oExcel.Application.Workbooks.Open pubSpreadsheetName Set xla = oExcel.Application DoEvents RowNumber = 2 NullCell = 0 ' Schedule = Format$(ImportDate, "mmmm dd, yyyy") ' PriorSchedule = Format$(ImportDate, "mmmm dd, yyyy") DoEvents SQLString = "truncate table ProductionScheduleImport" DoCmd.RunSQL SQLString DoEvents ' ImportProductionScheduleSpreadsheet Do While NullCell < 50 CellText = xla.Range("B" + Trim$(Str$(RowNumber))).Value DoEvents If Len(CellText) > 0 Then Schedule = xla.Range("A" + Trim$(Str$(RowNumber))).Value Debug.Print Schedule; " - "; If Len(Trim$(Schedule)) > 0 Then If IsDate(Schedule) = False Then Schedule = "" End If Else Schedule = "" End If Needed = xla.Range("B" + Trim$(Str$(RowNumber))).Value If IsDate(Needed) Then RepackNumber = xla.Range("D" + Trim$(Str$(RowNumber))).Value EstimatedHours = xla.Range("N" + Trim$(Str$(RowNumber))).Value Warehouse = xla.Range("E" + Trim$(Str$(RowNumber))).Value ProductCode = xla.Range("F" + Trim$(Str$(RowNumber))).Value Description = xla.Range("G" + Trim$(Str$(RowNumber))).Value Label = xla.Range("H" + Trim$(Str$(RowNumber))).Value SizeCode = xla.Range("I" + Trim$(Str$(RowNumber))).Value Cases = xla.Range("J" + Trim$(Str$(RowNumber))).Value Pounds = xla.Range("M" + Trim$(Str$(RowNumber))).Value Comments = xla.Range("R" + Trim$(Str$(RowNumber))).Value BuyerCode = xla.Range("O" + Trim$(Str$(RowNumber))).Value InternalUPC = xla.Range("P" + Trim$(Str$(RowNumber))).Value ExternalUPC = xla.Range("Q" + Trim$(Str$(RowNumber))).Value ' ImportProductionScheduleSpreadsheet DoEvents SQLString = "execute ImportScheduledRepack " If Len(Trim$(Schedule)) > 0 Then SQLString = SQLString & "'" & Trim$(Schedule) & "', " Else SQLString = SQLString & "Null, " End If SQLString = SQLString & "'" & Trim$(Needed) & "', " SQLString = SQLString & Trim$(RepackNumber) & ", " SQLString = SQLString & Trim$(Warehouse) & ", " SQLString = SQLString & "'" & Trim$(ProductCode) & "', " SQLString = SQLString & "'" & Trim$(Description) & "', " SQLString = SQLString & "'" & Trim$(Label) & "', " SQLString = SQLString & "'" & Trim$(SizeCode) & "', " SQLString = SQLString & Trim$(Cases) & ", " SQLString = SQLString & Trim$(Pounds) & ", " SQLString = SQLString & Trim$(EstimatedHours) & ", " SQLString = SQLString & IIf(Len(Trim$(Comments)) = 0, "Null, ", "'" & ForceSingleQuotes(Trim$(Comments)) & "', ") SQLString = SQLString & "'" & Trim$(BuyerCode) & "', " SQLString = SQLString & "'" & Trim$(InternalUPC) & "', " SQLString = SQLString & "'" & Trim$(ExternalUPC) & "'" Debug.Print SQLString On Error GoTo LogBadSQL DoCmd.RunSQL SQLString ResumeImport: On Error GoTo 0 DoEvents End If NullCell = 0 Else NullCell = NullCell + 1 End If ' ImportProductionScheduleSpreadsheet RowNumber = RowNumber + 1 Loop DoEvents oExcel.ActiveWorkbook.Close False DoEvents oExcel.Quit DoEvents Exit Sub LogBadSQL: DoCmd.RunSQL "insert into ProductionScheduleImportFailureLog (SpreadsheetRow, SQLStatement) values (" & Trim$(Str$(RowNumber)) & ", '" & ForceSingleQuotes(SQLString) & "')" pubImportStatus = pubImportStatus + 1 Resume Next End Sub