VBA code for pulling data from a closed workbook w/o opening it and direct insert into database

[bharat kumar]

here is code but i don't want to open it in current workbook ............

Sub GetDataFromClosedBook()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[Newbook.xls]Sheet1'!$A$1:$C$12" '<< change as required
'link to worksheet
With ThisWorkbook.Worksheets(1).Range("A1:C12") '<< change as required
.Formula = mydata
'convert formula to text
.Value = .Value
Dim conn As New ADODB.Connection
Dim iRowNo As Integer
Dim RowCount As Integer
conn.Provider = "sqloledb"
'conn.Properties("Prompt") = adPromptAlways
conn.Open "Data Source=10.10.1.137;Initial Catalog=Goldmine;user id=sa;password=11"
iRowNo = 2
RowCount = 1
Do Until .Cells(iRowNo, 1) = ""
Acno = .Cells(iRowNo, 1)
batchno = .Cells(iRowNo, 2)
Amount = .Cells(iRowNo, 3)
.Cells(1, 6) = RowCount
Dim Qu As String
Qu = "insert into dbo.rough (Account_No,batchno,Amount) values ('" & Acno & "', '" & batchno & "', '" & Amount & "')"
conn.Execute (Qu)
iRowNo = iRowNo + 1
RowCount = RowCount + 1
DoEvents
Loop
End With
End Sub

 

[Worf]

Welcome to the Board

See if this thread helps; it features another ADO example and a function to retrieve data from closed workbooks.

http://www.mrexcel.com/forum/excel-...rksheets-closed-workbook-without-opening.html