I have a peculiar issue with a single user who is using a small application I created with VB.NET (VS 2010). The issue is only seen on his machine and it used to work before...The application is currently working fine for everyone else. The only change is that he recently upgraded from MS Office 2003 to 2010...this may offer a clue, but I have not been able to find a solution.
What the app does (in a nutshell):
Searches on our network for specific Excel files, then uses the ADODB CopyFromRecords et to get the values from specific cell(s) out of those files and copies them into an Excel workbook under it's control.
While the app is running on his machine, everything runs normally except that for pretty much all the files that it would normally copy values from it throws the exception message:
Here is a snippet of the Try...Catch routines where the exception happens:
and the SQL_Extract Subroutine that is being called...
Now I know that the connection to Excel (from VB.NET app) is fine because it's actually writing the exception error details into the sheet I reserved for errors. What's throwing me for a loop is why it cannot copy the record from the sheet on the network into that Excel file? Something to do with a missing/corrupt library on the users machine? Something to do with his new version of Excel? (Note: Other users use Excel 2010 with no issues)
Any help or insight would be appreciated as I am a "self-taught" programmer...ev en just discussing the issue might help me think about a solution even if you cannot offer one.
Thank you!!!
-Derek
What the app does (in a nutshell):
Searches on our network for specific Excel files, then uses the ADODB CopyFromRecords et to get the values from specific cell(s) out of those files and copies them into an Excel workbook under it's control.
While the app is running on his machine, everything runs normally except that for pretty much all the files that it would normally copy values from it throws the exception message:
Code:
"No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE))"
Code:
Try
My.Application.DoEvents()
intBATFileCount = intBATFileCount + 1
lblFilesProcessed.Text = "Number of files processed: " & intBATFileCount
rtbRunningLog.AppendText("Processing file: " & strBATFile & vbLf)
'Grab SiteID and Last Write Time
wsData.Range("A" & intRowDataTarget).Value = My.Computer.FileSystem.GetName(strBATFile)
wsData.Range("B" & intRowDataTarget).Value = IO.File.GetLastWriteTime(strBATFile).ToShortDateString
strReference = wsTranslations.Range("A" & intTranslationsRefRow).Value
'Open ADO Connection to Excel file and leave it open until all data is extracted.
Dim ADOConnection As New ADODB.Connection
stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strBATFile & ";" & "Extended Properties='Excel 8.0;HDR=No'"
ADOConnection.ConnectionString = stCon
ADOConnection.Open()
Do Until strReference = "" 'Do until no more data to extract from currently connected file.
My.Application.DoEvents()
'CONVERT INTEGER TO CHARACTER
' If 1-26, then this is an easy conversion
If intColDataTarget < 27 Then
strGetExcelColumn = Chr(intColDataTarget + 64)
Else
' Now we have to account for AA-ZZ
iBeg = intColDataTarget \ 26 ' Get the first letter
iEnd = intColDataTarget Mod 26 ' Get the second letter
If iEnd = 0 Then
' If iEnd is 0, then it is Z, which should be 26
iEnd = 26
'** you need to subtract 1 from the initial letter otherwise your lettering will be the next letter in the alphabet
iBeg = iBeg - 1
End If
strGetExcelColumn = Chr(iBeg + 64) & Chr(iEnd + 64)
End If
'Define the SQL string, call the command executer, and paste the recordset into the data sheet...but leave connection open!!!
stSQL = "SELECT * From [" & strReference & "]"
SQL_ExtractData(ADOConnection, stSQL, rst)
wsData.Range(strGetExcelColumn & intRowDataTarget).CopyFromRecordset(rst)
stSQL = Nothing
ADOCommand = Nothing
rst.Close()
intTranslationsRefRow = intTranslationsRefRow + 1
intColDataTarget = intColDataTarget + 1
strReference = wsTranslations.Range("A" & intTranslationsRefRow).Value
Loop
ADOConnection = Nothing
Catch err As Exception
'Routine to save error details to "Errors" tab in the sheet
wsErrors.Range("A" & intErrRowIndex.ToString).Value = My.Computer.FileSystem.GetName(strBATFile).ToString
wsErrors.Range("B" & intErrRowIndex.ToString).Value = IO.File.GetAttributes(strBATFile).ToString
wsErrors.Range("C" & intErrRowIndex.ToString).Value = err.Message.ToString
wsErrors.Range("D" & intErrRowIndex.ToString).Value = err.Data.ToString
intBATFileErrorCount = intBATFileErrorCount + 1
intRowDataTarget = intRowDataTarget - 1
intBATFileCount = intBATFileCount - 1
lblErrors.Text = "Number of Errors: " & intBATFileErrorCount
intErrRowIndex = intErrRowIndex + 1
End Try
Code:
Public Sub SQL_ExtractData(ByVal ADOConnection As ADODB.Connection, ByVal stSQL As String, ByRef rst As ADODB.Recordset)
Dim ADOCommand As New ADODB.Command
ADOCommand.CommandType = ADODB.CommandTypeEnum.adCmdText
ADOCommand.ActiveConnection = ADOConnection
ADOCommand.CommandText = stSQL
rst = ADOCommand.Execute()
End Sub
Any help or insight would be appreciated as I am a "self-taught" programmer...ev en just discussing the issue might help me think about a solution even if you cannot offer one.
Thank you!!!
-Derek