ADO Insert keeps leaving a single quote in excel

[bradyboyy88]

So everytime I use ADO insert using the following code it causes a ' infront of my written value in the excel sheet. I have 2 functions, one to query and one to write to the excel file, and then test to run these sub routines. So in my test subroutine when i look at the excel file it shows 'TEST5 instead of just TEST5. I cant seem to figure this out. Thanks!

Option Explicit


Private Function SQLQueryDatabase(SQLQuery As String, StrDBPath As String, FieldNumber As Integer)




    'Declare Variables
    Dim oConn        As Object
    Dim oRs          As Object
    Dim sConn        As String
   
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
                                 
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
   
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    
    'Connect to the database
    oConn.Open sConn
    
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Update the textbox with the only returned value
    If oRs.EOF And oRs.BOF Then


        SQLQueryDatabase = Null
        
    Else
    
        SQLQueryDatabase = oRs(FieldNumber).Value
    
    End If
    'Close Connection
    oConn.Close
    
    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing




End Function


Private Sub SQLWriteDatabase(SQLQuery As String, StrDBPath As String)


    'Declare Variables
    Dim oConn        As Object
    Dim oRs          As Object
    Dim sConn        As String
   
    'Define Connection String by inputting StrDBPath into a larger string (Works for Excel DB)
    sConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & StrDBPath & "';" & _
             "Extended Properties=""Excel 12.0;HDR=YES;ReadOnly=0;"";"
                                 
    'Create Connection
    Set oConn = CreateObject("ADODB.Connection")
    oConn.Mode = 3
   
    'Create RecordSet
    Set oRs = CreateObject("ADODB.Recordset")
    
    'Connect to the database
    oConn.Open sConn
    
    'Open Record Set by executing SQL
    oRs.Open SQLQuery, oConn
    
    'Close Connection
    oConn.Close
    
    'Clear Memory
    Set oConn = Nothing
    Set oRs = Nothing


End Sub


Sub test()
Dim SQLString As String
Dim testvalue As Variant
            
            SQLString = "Select * FROM [SHEET1$] WHERE NEW_NAME= 'TEST5'"
        
            If IsNull(SQLQueryDatabase(SQLString, "C:\Users\User\Desktop\Test\book5.xlsx", 2)) Then
            
                SQLString = "INSERT INTO [SHEET1$] (NEW_NAME, ROUND6) VALUES ('TEST5','22')"
                Call SQLWriteDatabase(SQLString, "C:\Users\User\Desktop\Test\book5.xlsx")
            
            Else
            
                SQLString = "UPDATE [SHEET1$] SET ROUND5='25' WHERE NEW_NAME= 'TEST5'"
                Call SQLWriteDatabase(SQLString, "C:\Users\User\Desktop\Test\book5.xlsx")
            
            End If
            
End Sub

 

[bradyboyy88]

Anyone? I would think this is a common problem.

 

[Norie]

How is Book5.xlsx structured?

ie field names etc

 

[bradyboyy88]

How is Book5.xlsx structured?

ie field names etc

Literally 1 sheet name sheet1 and the fields are typed AS FOLLOWS :

COMPANY NEW_NAME ROUND1 ROUND2 ROUND3 ETC..

I have not formatted any cells or anything. I know ' indicates its a text datatype so maybe its related to that?

 

[Norie]

I've just run the code and I'm not getting a ' in front of anything.

The only 'problem' I can see is that the value 22 is being treated as text but I think the only reason for that is the use of the '' in the SQL.

 

[bradyboyy88]

I've just run the code and I'm not getting a ' in front of anything.

The only 'problem' I can see is that the value 22 is being treated as text but I think the only reason for that is the use of the '' in the SQL.

Oddly enough your right. It works but only if the excel sheet is not open haha. If I have it open then it adds the ' . I removed the '' around the number 22 but still seems to show up as text which is annoying.

The code does not work in logic though. It keeps inserting TEST5 for some reason if i run the code multiple times. I was hoping to make it not insert it if there was already a record there but instead append the record with the round number

 

[Norie]

The Fields collection of a recordset object is 0 indexed which means that in your code this refers to the third field, not the second.

oRs(FieldNumber).Value

 

[bradyboyy88]

The Fields collection of a recordset object is 0 indexed which means that in your code this refers to the third field, not the second.

oRs(FieldNumber).Value

You are a genius man. Any idea on the 22 showing up as a string still even when I remove the ' ' ? That is my last problem but you have saved my project!

 

[Norie]

Not at the moment, still having problems with this new computer - how can it have 65MB of corrupt files?

PS Not a lot of people use this method to write to Excel files, especially open ones.

 

[bradyboyy88]

Not at the moment, still having problems with this new computer - how can it have 65MB of corrupt files?

PS Not a lot of people use this method to write to Excel files, especially open ones.

Yea, I am actually using this for excel temporarily. I am building my library of transfer tools that will also work with access which is kind of my main reason. I wish I could return the favor with some help but 65mb of corrupt files sounds like more headache that its worth. Id just roll back your system to when they were working haha. Hopefully you guys do backups!!!!