[EXCEL] Open and copy from workbook specified in existing cells / 2010 upgrade problem

[morten.bay]

Hi.

I am fairly new to VBA.

I created a VBA code in Excel 2007 to automatically open and copy data from a workbook located on a network drive into an existing workbook. The name and location of the network worbook was specified in cells in the existing workbook (See below). It worked perfectly fine in 2007, but after upgrading to 2010 I get the error that the network worbook can not be found. I have checked that all file type specifications are correct (e.g. .xls and .xlsx. and the location of the newtwork has not changed. My guess is that it the code simply doesn't work in the new version, but how should it be changed then.

Thank you so much

My code

ChDir Range("D49")
 Workbooks.Open Filename:=Range("B49")
 sWin = Application.Workbooks("Existing workbook.xlsm").Worksheets("Frontpage").Range("B49")
 Application.Windows(sWin).Activate
 sheets("Sheet1").Select
 Range("A1:II1200").Select
 Selection.Copy
 Windows("Existing workbook.xlsm").Activate
 sheets("Stress").Select
 Range("A1").Select
 ActiveSheet.Paste
 Application.CutCopyMode = False

 sWin = Application.Workbooks("Existing workbook.xlsm").Worksheets("Frontpage").Range("B49")
 Application.Windows(sWin).Activate
 ActiveWindow.Close

[Richard Buttrey]

Please upload the workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

[vtxvtx]

As above without more info its difficult to see exactly whats happening,

However, if (restrictions permitting) all involved workbooks could be changed to the .xls format from .xlsm/.xlsx I've found that often fixes problems. So it may be worth trying.

Andy

[morten.bay]

Thank you very much for your help. It is much appriciated. Unfortunately, due to strict security policy at work, I can't provide a copy of the workbook.

However I think another user provided a solution in this thread, so I'm trying to go with that.

[JosephP]

duplicate thread so locked