Upload from Excel vba to SQL Server - generic excel file not working

Question

I want to create a "generic" way of uploading data to a SQL Server database via Excel upload. In my Excel file I have two sheets - one with data (WS_data) and one with the database table design (WS_Table_Design) (e.g. rows of column names).

It is been a while since I coded VBA, but this used to work (I think?) - what am I thinking wrong here?

Errors comes when I want to "post" the values into the database through .Fields([Column]).Value

Function NullCheck(x)

On Error Resume Next
If x = "" Then
    NullCheck = Null
    Else
    NullCheck = x
End If

End Function

Sub Upload_To_DB()

Dim WS_Data, WS_Table_Design As Worksheet

Dim Curent_Row, Curren_Column, Max_Datarows, i, j As Integer
Dim Array_Table_Design()


Set WS_Data = Worksheets("Data")
Set WS_Table_Design = Worksheets("DB_Table_Design")

'List Columns from Table design in array

ReDim Array_Table_Design(0 To 0)
Current_Row = 1
i = 0
    Do While WS_Table_Design.Cells(Current_Row, 1).Value <> ""

        If WS_Table_Design.Cells(Current_Row, 1).Value > 0 Then
            ReDim Preserve Array_Table_Design(0 To i)
            Array_Table_Design(i) = WS_Table_Design.Cells(Current_Row, 1).Value
            i = i + 1
        End If

    Current_Row = Current_Row + 1
    Loop

'End List Columns

'Find number of rows to upload
Current_Row = 2 'Row where data starts
Max_Datarows = 0
Do While WS_Data.Cells(Current_Row, 1).Value <> ""
    Max_Datarows = Max_Datarows + 1

Current_Row = Current_Row + 1
Loop
'End find number of rows to upload

'Upload data according to Table design and Data sheet

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sqlstring As String

Set con = New ADODB.Connection
con.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Datawarehouse;Data Source=server3"
Set rs = New ADODB.Recordset

rs.Open "select * from FactGL", con, adOpenStatic, adLockOptimistic

Current_Row = 2
Current_Column = 1
i = 0
j = 0
For j = 0 To 5 'Max_Datarows

    rs.AddNew
    For i = 0 To UBound(Array_Table_Design)

'Error comes here - why can't I add data to the database Table column defined in "Array_Table_Design?
            rs.Fields("Array_Table_Design(i)").Value = NullCheck(WS_Data.Cells(Current_Row, i + 1).Value)
            i = i + 1
        Next i
        rs.Update
        Current_Row = Current_Row + 1
    Next j

    End Sub

"Errors come" is not a very useful description of what happens when you run your code. — Tim Williams
– Tim Williams, Commented Feb 2, 2020 at 22:14
The text of the error message is as important as the number. — Tim Williams
– Tim Williams, Commented Feb 3, 2020 at 17:08

Tim Williams · Accepted Answer · 2020-02-02 22:15:20Z

2

rs.Fields("Array_Table_Design(i)").Value

should be

rs.Fields(Array_Table_Design(i)).Value

answered Feb 2, 2020 at 22:15

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user12829469 Over a year ago

Thank you. I tried this - and get "run time error 3265" back. I take this as if the connection does not recongize the table column/field I want to add data to?

user12829469 Over a year ago

Thank you for the follow up! The error 3265 is now not that relevant anymore. I found out that the .Fields() function does not need [] for column names, e.g. I had added column names, that were invalid. I simply removed the [] from my "table design" list. Secondly, I discovered that I have excel date format (e.g. numbers) that I am trying to upload to a SQL datetime format. Which will not work. E.g. I will have to make a function checking whether it is an excel date that should be converted to SQL datetime (not sure yet - maybe I will simply not make a generic function for this)...

Collectives™ on Stack Overflow

Upload from Excel vba to SQL Server - generic excel file not working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related