Another xls upgrade issue.....

[VegasNath]

I'm not having much fun in converting from xls to xlsm. My latest issue:

Code: Select all

strFile2 = "5. Month " & strMnth & " Daily Walk *.xlsm"
'Get name of actual file
strFile2 = Dir(strPath2 & strFile2)

'Open Daily Walk file
Set wb2 = Workbooks.Open(Filename:=strPath2 & strFile2, Password:=Password1, WriteResPassword:=Password2)

........

'Create Formulas - "Detail" data rows

    arrList = Array(0, 31, 62, 93, 124, 155, 190)
    i = wb2.Sheets("Detail").Range("B31").End(xlUp).Row
    For Each n In arrList
        wb2.Sheets("Detail").Range("B" & (i + n) & ":IV" & (i + n)).Copy Destination:=wb2.Sheets("Detail").Range("B" & (i + n + 1) & ":IV" & (i + n + 1))
    Next n
    Calculate

In the For Next loop, the copy occurs 6 times. I am being prompted for password 1 on 5 of the 6 copy..... lines. This never happened in xls. Any idea's why?

I am slowly beginning to detest xlsm.

[HansV]

There must be something else causing this - I recreated your scenario in Excel 2007, and the code ran without a problem. But without seeing the workbooks involved, it's impossible what is behind it.

[VegasNath]

Thankyou for your kind effort Hans. I had stray external links that were causing the issue. If I was working in 2003 I would have notived that straight away. I was looking in 'Existing Connections' on the data tab in 2007 which was reporting nothing. I guess that is not the correct place for viewing links.

[HansV]

The Connections dialog is for connections to databases and similar. The equivalent of the Edit | Links dialog from Excel 2003 and before is Office button > Prepare > Edit Links to Files. This option is only available if the active workbook contains links to other files.

[Jan Karel Pieterse]

The edit links button is in the same group as the Connections button...

[VegasNath]

Not an easy place to find it! Thanks both.