Extracting Data Without Opening Workbooks

[Varne]

Hello!

I have these below given codes that extract data from Excel files without
opening those files. The problem is if those files are password protected
these codes do not work. I tried to fit in the Password:=â€fsgsgâ€(example)
but no use.
Could someone help?
Thanks.

Sub AccessingExcel()

Dim recordset As ADODB.recordset
Dim SQL As String
Dim connectionstring As String

connectionstring = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & "Book1" & ".xls; " & _
"Extended Properties=Excel 8.0;"

SQL = _
"select * from (B1:B50000)"

Set recordset = New ADODB.recordset

recordset.Open SQL, connectionstring

Cells(1, 1) = recordset.Fields(0)

recordset.Close

End sub

 

[john]

I can't help with that approach but you can extract data from closed workbbok
using formula which I think, is not affected by password problems (although
could be wrong!)

something like following may work for you:



Sub GetData()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[mybook.xls]Sheet1'!$B$2:F12" '<< change as required

'link to worksheet
With ThisWorkbook.Worksheets(1).Range("B2:F12") '<< change as required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

just an idea, sorry if of no help.

 

[ryguy7272]

I hope it's not too late to join the party...

Take a look at this:
http://www.rondebruin.nl/ado.htm

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

I can't help with that approach but you can extract data from closed workbbok
using formula which I think, is not affected by password problems (although
could be wrong!)

something like following may work for you:



Sub GetData()
Dim mydata As String
'data location & range to copy
mydata = "='C:\[mybook.xls]Sheet1'!$B$2:F12" '<< change as required

'link to worksheet
With ThisWorkbook.Worksheets(1).Range("B2:F12") '<< change as required
.Formula = mydata

'convert formula to text
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
Application.CutCopyMode = False
End Sub

just an idea, sorry if of no help.
--
jb

Hello!

I have these below given codes that extract data from Excel files without
opening those files. The problem is if those files are password protected
these codes do not work. I tried to fit in the Password:=â€fsgsgâ€(example)
but no use.
Could someone help?
Thanks.

Sub AccessingExcel()

Dim recordset As ADODB.recordset
Dim SQL As String
Dim connectionstring As String

connectionstring = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.Path & "\" & "Book1" & ".xls; " & _
"Extended Properties=Excel 8.0;"

SQL = _
"select * from (B1:B50000)"

Set recordset = New ADODB.recordset

recordset.Open SQL, connectionstring

Cells(1, 1) = recordset.Fields(0)

recordset.Close

End sub