Importing Excel Workbook Into SQL Server Table Using VBA & OWC10.D

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: RichK (RichK_at_discussions.microsoft.com)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 05:43:02 -0700

I have an MS Access 2002 project that exports spreadsheets to Excel using the
OWC10.Dll Functions. The code is listed below:
******************************************************
            With Rs_Spread***
                .Source = "dbo.Temp_CasesToSC"
                .Open .Source, CurrentCnn, adOpenDynamic, adLockOptimistic
                Set Sp_SCID = New OWC10.Spread***
                With Sp_SCID
                    ' set up the name of the spread***

                    .Active***.Name =
Rs_WhichDIFSCIDs.Fields("SCID").Value & "-" &
Rs_WhichDIFSCIDs.Fields("DIFYYMM").Value &
Format(Rs_WhichDIFSCIDs.Fields("DIFSeqNum").Value, "000")

                    ' copy the data from the ado recordset

                    .Active***.Cells.CopyFromRecordset Rs_Spread***

                    ' set up the SC Outcome Description and the OSC to be
unprotected

                    .Active***.UsedRange.Columns(11).Locked = False
                    .Active***.UsedRange.Columns(12).Locked = False

                    ' set the titles for the SC Outcome Description and OSC
To Be protected

                    .Active***.Cells(1, 11).Locked = True
                    .Active***.Cells(1, 12).Locked = True

                    ' Delete the unecessary sheets

                    .Worksheets.Item("Sheet2").Delete
                    .Worksheets.Item("Sheet3").Delete

                    ' Protect the work***

                    .Active***.Protect

                    ' export the work*** to a Excel file

                    .Export Filename, ssExportActionNone,
ssExportAsAppropriate

                End With
                .Close
                Set Sp_SCID = Nothing
            End With
****************************************************

This code works well to export the spread*** files. My question is "How
do I import these same files back into SQL Server Using MS Access 2002?"
When I use the TransferSpread*** method the following error message is
displayed:
"Can Not Access Information in the File. Please make sure that it is in the
correct format"

I Believe that this is because TransferSpread*** can not interperate the
Workbook files created by the above code. I have also tried to import using
the HTML method but all this gives me is junk.

Quantcast