Mqcro loop / password code /

  • Thread starter Thread starter Snoopy
  • Start date Start date
S

Snoopy

Hey guys
I've made a summary-file to collect data from several files in a
commen folder.
So far I've hardcoded my macro to pick up the datas from every singel-
named file.
The same prosedyre is used on every singel file.
This must be wiser to perform this by using a loop-statement...but
how?
There are 9 separate files to handle (I show my reference to the 2 of
them)
Some of the files may be protected by password.
Are there any way to "tell" the macro to still open it?
When running my primitiv macro a messagebox pops up and say there is a
lot of copyed data on the clipboard. I want the macro to run through
the lot whithout stopping.
How do I do this?

I will be very thankfull for your help

Regards
Snoopy

The tricky part of my macro:

'Merk "Petter - protected file - opens with password: "123"
Application.StatusBar = "Petter"
Workbooks.Open Filename:="E:\Petter.xls"

Sheets("Register").Select
Range("A2").Select

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
Selection.Copy
Range("A2").Select

Windows("Rapport.xls").Activate

Sheets("Samlet").Select

Selection.Offset(0, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.End(xlDown).Select
Selection.Offset(1, 0).Range("A1").Select
Windows("Petter.xls").Activate
ActiveWindow.Close

'Merk "Rita"
Application.StatusBar = "Rita"
Workbooks.Open Filename:="E:\Rita.xls"

Sheets("Register").Select
Range("A2").Select

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
Selection.Copy
Range("A2").Select

Windows("Rapport.xls").Activate

Sheets("Samlet").Select

Selection.Offset(0, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.End(xlDown).Select
Selection.Offset(1, 0).Range("A1").Select
Windows("Rita.xls").Activate
ActiveWindow.Close

'Merk "Sidsel"
Application.StatusBar = "Sidsel"
Workbooks.Open Filename:="E:\sidsel.xls"

Sheets("Register").Select
Range("A2").Select

On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0

Set tbl = ActiveCell.CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
tbl.Columns.Count).Select
Selection.Copy
Range("A2").Select

Windows("Rapport.xls").Activate

Sheets("Samlet").Select

Selection.Offset(0, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Selection.End(xlDown).Select
Selection.Offset(1, 0).Range("A1").Select
Windows("sidsel.xls").Activate
ActiveWindow.Close
 
Snoop,

There's a lot of code in your post that has nothing to do with your questions, but I think
this is what you're after:

Dim i As Integer

Dim MyFiles(2) ' three files, 0-2
Dim MyPasswords(2) ' could have been a single 2-dimensional array for both

' Load up file names
MyFiles(0) = "a.xls"
MyFiles(1) = "b.xls"
MyFiles(2) = "c.xls"

' Load up passwords
MyPasswords(0) = "ab"
MyPasswords(1) = "" ' (not password protected)
MyPasswords(2) = ""

For i = 0 To (UBound(MyFiles)) ' Start looping through the workbooks
Workbooks.Open Filename:=MyFiles(i), ReadOnly:=True, Password:=MyPasswords(i)

' Do your stuff here

' Close the file
Application.CutCopyMode = False ' prevent message about clipboard
ActiveWorkbook.Close SaveChanges:=False
Next i
End Sub

--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
 
Snoop,

There's a lot of code in your post that has nothing to do with your questions, but I think
this is what you're after:

Dim i As Integer

Dim MyFiles(2)  ' three files, 0-2
Dim MyPasswords(2) ' could have been a single 2-dimensional array for both

' Load up file names
MyFiles(0) = "a.xls"
MyFiles(1) = "b.xls"
MyFiles(2) = "c.xls"

' Load up passwords
MyPasswords(0) = "ab"
MyPasswords(1) = ""   ' (not password protected)
MyPasswords(2) = ""

For i = 0 To (UBound(MyFiles))  '  Start looping through the workbooks
  Workbooks.Open Filename:=MyFiles(i), ReadOnly:=True, Password:=MyPasswords(i)

  ' Do your stuff here

  ' Close the file
  Application.CutCopyMode = False ' prevent message about clipboard
  ActiveWorkbook.Close SaveChanges:=False
  Next i
End Sub

--
Regards from Virginia Beach,

Earl Kiosterudwww.smokeylake.com



























– Vis sitert tekst –
Click to expand...

Thanks a lot :)))

Best regards
Snoopy
 
Back
Top