Who Can Solve This Problem?

[abduhsuryadi]

I have a problem with VBA EXCEL. Who can solve?

The problem is: I write formula to CLOSED WORKBOOKS those have password.

The code:

ActiveCell.Formula="='D:\myFolder\[myExcel.xlsx]Sheet1'!$A$1"

Every time I excute the code, it's ask a password.
How can I supply a password automatically FROM VBA when I excute that CODE? cause I have more than 20 workbooks.

Thanks before...

[Kenneth Hobs]

IF you mean write data to closed workbooks, you need to use an ADO method or open each workbook. Of course you must supply the password. IF you mean read data, then this will work.

[vba]Sub ReadDataFromAllWorkbooksInFolder()
Dim FolderName As String, wbName As String, r As Long, cValue As Variant
Dim wbList() As String, wbCount As Integer, i As Integer
FolderName = ThisWorkbook.path
' create list of workbooks in foldername
wbCount = 0
wbName = Dir(FolderName & "\" & "*.xls")
While wbName <> ""
wbCount = wbCount + 1
ReDim Preserve wbList(1 To wbCount)
wbList(wbCount) = wbName
wbName = Dir
Wend
If wbCount = 0 Then Exit Sub
' get values from each workbook
r = 0
Workbooks.Add
For i = 1 To wbCount
r = r + 1
cValue = GetInfoFromClosedFile(FolderName, wbList(i), "Sheet1", "A1")
Cells(r, 1).Formula = wbList(i)
Cells(r, 2).Formula = cValue
Next i
End Sub

Private Function GetInfoFromClosedFile(ByVal wbPath As String, _
wbName As String, wsName As String, cellRef As String) As Variant
Dim arg As String
GetInfoFromClosedFile = ""
If Right(wbPath, 1) <> "\" Then wbPath = wbPath & "\"
If Dir(wbPath & "\" & wbName) = "" Then Exit Function
arg = "'" & wbPath & "[" & wbName & "]" & _
wsName & "'!" & Range(cellRef).Address(True, True, xlR1C1)
On Error Resume Next
GetInfoFromClosedFile = ExecuteExcel4Macro(arg)
End Function

[/vba]

[abduhsuryadi]

Thanks so much for the feedback.
the problem is if the workbooks have password.

i have used the code and i tried it in some protected workbooks. if i excude the code, it ask to supply a password for each workbooks

How can we supply password automatically from VBA? thanks

[Kenneth Hobs]

My code works or I would not have posted it as I most always test my code before posting. Since I can not predict what your setup is, I can not code for every scenario. You will need to explain more as to why it does not work for you.

You can not use it as a UDF. Did you put the code in a Module? You can remove the word Private but it works either way. You can trigger filling in values by certain events obviously or just run it manually.

IF you remove code, how can you expect it to work?

IF you mean that the workbook with that code needs a password to accept the output, there are several ways to do that. The easiest way is this:
[VBA]Sheet1.Protect "ken", userinterfaceonly:=True[/VBA]

[abduhsuryadi]

My Problem is:

I have two workbooks, workbook1 and workbook2. workbook2 has password.
I want to get workbook2 data from workbook1. How can I supply password from vba while workbook2 (without open it) is closed to get data from it?

thanks

[Kenneth Hobs]

My code works or I would not have posted it as I most always test my code before posting.

IF you want to do it the slow way that requires that you know the password, then supply the password in the Workbooks Open method.

[abduhsuryadi]

My current workbook name's is workbook1, and the other workbook2, workbook3, workbook4 until workbook30. i put it in one folder. each workbook has password except workbook1. the password is "mypassword"

I open workbook2 with password from workbook1 VBA, and then copy workbook2 data then activate workbook1 and paste in it. then close workbook2. then i open workbook3 and i do like what i have done in workbook2 and so on.

how can i make it more simple? can i get data from each workbook without open it?

note: I want the workbook password remain, and don't want to throw it

it's my code:
[vba]
Sub GetData()

Dim sFolder As String
Dim sFileName As String

sFolder = "D:\MyFiles"

sFileName = Dir(sFolder)

Range("A1").Select

Do While sFileName > ""
On Error Resume Next
Workbooks.Open sFolder & "\" & sFileName, , , , "mypassword"
Range("A1").Copy
Workbooks("workbook1").Activate

ActiveCell.PasteSpecial xlPasteValues

ActiveCell.Offset(1, 0).Select

Workbooks(sFileName).Close True

sFileName = Dir()

Loop

End Sub

[/vba]
Note: I'm so sorry for my bad language. I'm Indonesian and I just lear English. I'm so sorry if the language is not polite. I need help from you all, excel master.
thanks for the help.

[snb]

Why using a password if you want to use the workbook's content ?

[abduhsuryadi]

i just want to know the solving of this problem.

so, is the answer "it's can't"?

[Aussiebear]

Is the password the same for all workbooks?

[mancubus]

hi,

i modified your code to copy cell "A1" content of all active sheets of all workbooks but workbook1 in the same folder.

[VBA]
Sub GetData()

Dim sFolder As String
Dim sFileName As String
Dim pass As String

pass = "mypassword"
sFolder = "D:\MyFiles\"
sFileName = Dir(sFolder & "*.xls") 'office 2003
' sFileName = Dir(sFolder & "*.xls") 'office 2007/2010

Range("A1").Select
Do While sFileName <> "" And sFileName <> ThisWorkbook.Name
On Error Resume Next
Workbooks.Open sFolder & "\" & sFileName, , , , pass
Range("A1").Copy
ThisWorkbook.Activate
ActiveCell.PasteSpecial xlPasteValues
ActiveCell.Offset(1, 0).Select
Workbooks(sFileName).Close False
sFileName = Dir()
Loop

End Sub
[/VBA]