I have the code (which works sometimes) to export an Excel named range to an SQL Database existing table, which I have pasted below:
Sometimes it works, sometimes it does not.
Error is TempRange is not found by VBA (Object not found).
I have no idea why it gives error randomly.
Pl help.
Regards,
Sometimes it works, sometimes it does not.
Error is TempRange is not found by VBA (Object not found).
I have no idea why it gives error randomly.
Pl help.
Regards,
Code:
Sub Update_sql_test()
Dim cnn As Object
Dim wbkOpen As Workbook
Dim rngName As Range
Set wbkOpen = ActiveWorkbook
LR = Range("A1").End(xlDown).Row
Debug.Print "LR is " & LR
CR = Range("A1").End(xlToRight).Column
Debug.Print "CR is " & CR
ActiveSheet.Select
Set rngName = Range(Cells(1, 1), Cells(LR, CR)) '.Select
rngName.Select
rngName.Name = "TempRange"
tr = Selection.Rows.Count - 1
Debug.Print "Total Records = " & tr & "Nos"
strFileName = wbkOpen.FullName
Set cnn = CreateObject("ADODB.Connection")
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
nsql = "INSERT INTO [odbc;Driver=SQL Server;Server=bhw1;Uid=user;Pwd=mypassword;Database=sqldb].sqltable"
njoin = " SELECT * from [TempRange]"
cnn.Execute nsql & njoin
cnn.Close
'=================================
Set wbkOpen = Nothing
End Sub