VBA to open different Excel file - doesn't work in Office 365

K

kubabocz

New Member
Joined
Oct 28, 2015
Messages
36
Hi Guys,

I have a Excel file which has a macro to open multiple spreadsheets based on the location specified within macro.

I used this file (and macro) for about a year and it was all good until we migrated to Windows 10 and Office 365.
All other macros seem to be fine but the most important one doesn't work.

I tried to use the below methods to open the other excel file:
Workbooks.Open ("C:\Users\81088836\Desktop\Book 1.xlsx")

Workbooks.Open Filename:= _
"H:\Group\R&D\CP&I RD158\Develop\General\Technical Resource Planning - input files\Packaging.xlsm"

Can you please advise what is the problem and how I can fix it.

Thank you
Jakub
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sort by date Sort by votes
The first thing I would do is go out to Windows Explorer and verify those two paths still exist, as they are written. Sometimes migrating to different version changes up the folder structures, especially on your C drive.

So check to see if you can browse to the following locations from that computer:
C:\Users\81088836\Desktop\
H:\Group\R&D\CP&I RD158\Develop\General\Technical Resource Planning - input files\
 
Last edited:
Upvote 0
That's the first thing I've checked, unfortunately it must be something else, I can browse both locations.
 
Upvote 0
What happens?
Do you receive some sort of error message, or does it just not work?
Can you step through the code line-by-line using the F8 key to see what is going on?
 
Upvote 0
Hi guys,

Please find the code below. It always stops in the line marked in red.
I get the standard error with option to debug and when I click on debug it highlights the "red" line
Code: 
Sub updateCP()
'
' update Macro


' Stop calculation
    Application.Calculation = xlManual
    Application.ScreenUpdating = False
    
'Unhide sheets
    Sheets("Projects").Visible = True
    Sheets("Master").Visible = True
    Sheets("ProjectsCalc").Visible = True
    Sheets("CP import").Visible = True
    
'Clearing vlookup
    Sheets("Projects").Select
    Range("L4:AS4").Select
    Selection.ClearContents


'Updating team projects and availability


    Sheets("Pr Develop").Select
    Range("A3:BZ10000").Select
    Selection.ClearContents
    Range("A3").Select
[COLOR=#ff0000]    Workbooks.Open Filename:= _[/COLOR]
[COLOR=#ff0000]        "H:\Group\R&D\CP&I RD158\Develop\General\Technical Resource Planning - input files\Product development.xlsm"[/COLOR]
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Projects").Select
    Range("A3:BZ10000").Select
    Selection.Copy
    Windows("Reporting file - Central Product.xlsm").Activate
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3").Select
    
            Sheets("CP import").Select
            Range("B2:BZ2500").Select
            Selection.ClearContents
            Range("A2").Select
            Windows("Product development.xlsm").Activate
            Sheets("Resource input").Select
            Range("B3:BZ590").Select
            Selection.Copy
            Windows("Reporting file - Central Product.xlsm").Activate
            Sheets("CP import").Select
            Range("B1043").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Range("A3").Select
                       
            
    Sheets("Pr Pack").Select
    Range("A3:BZ10000").Select
    Selection.ClearContents
    Range("A3").Select
    Workbooks.Open Filename:= _
        "H:\Group\R&D\CP&I RD158\Develop\General\Technical Resource Planning - input files\Packaging.xlsm"
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Projects").Select
    Range("A3:BZ10000").Select
    Selection.Copy
    Windows("Reporting file - Central Product.xlsm").Activate
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3").Select
            
            Sheets("CP import").Select
            Range("B595").Select
            Windows("Packaging.xlsm").Activate
            Sheets("Resource input").Select
            Range("B3:BZ450").Select
            Selection.Copy
            Windows("Reporting file - Central Product.xlsm").Activate
            Sheets("CP import").Select
            Range("B595").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Range("A3").Select
            
            
    Sheets("Pr CAP 3").Select
    Range("A3:BZ10000").Select
    Selection.ClearContents
    Range("A3").Select
    Workbooks.Open Filename:= _
        "H:\Group\R&D\CP&I RD158\Develop\General\Technical Resource Planning - input files\CAP 3.xlsm"
    ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
    Sheets("Projects").Select
    Range("A3:BZ10000").Select
    Selection.Copy
    Windows("Reporting file - Central Product.xlsm").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3").Select
    
            Sheets("CP import").Select
            Range("B3").Select
            Windows("CAP 3.xlsm").Activate
            Sheets("Resource input").Select
            Range("B3:BZ593").Select
            Selection.Copy
            Windows("Reporting file - Central Product.xlsm").Activate
            Sheets("CP import").Select
            Range("B3").Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Sheets("DemVsRes").Select
            Range("B3").Select
  
  ' update MASTER


    
     Application.Calculation = xlAutomatic
     Sheets("Master").Select
     Range("A2:AH10000").Select
     Selection.ClearContents
     Range("AI3:AI10000").Select
     Selection.ClearContents
     
     Range("A2").Select
     Workbooks.Open Filename:= _
         "H:\Group\R&D\CP&I RD158\Develop\General\Planning 2Y+C\Project Tracker\Master File.xlsm"
     ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
     Sheets("TRP Input").Select
     Range("A2:AH10000").Select
     Selection.Copy
     Windows("Reporting file - Central Product.xlsm").Activate
     Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


     
    Range("AI2").Select
    lRw = Range("A" & Rows.Count).End(xlUp).Row
    Range("AI2", "AI" & lRw).FillDown
    
        Range("A1:AI10000").Select
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Clear
    
    ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range("A2:A10000") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range("A1:AI10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A2").Select
    
' Update 8 projects
 Application.DisplayAlerts = False
                    ' Cap 3


                    Windows("CAP 3.xlsm").Activate
                    If ActiveWorkbook.MultiUserEditing Then
                        ActiveWorkbook.ExclusiveAccess
                    End If
                    ActiveWorkbook.Unprotect "planning"
                    Sheets("8 projects").Visible = True
                    Sheets("8 projects").Select
                    Range("A2:E49").Select
                    Selection.Copy
                    Windows("Reporting file - Central Product.xlsm").Activate
                    Sheets("8 projects").Select
                    Range("A2:E49").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    Windows("CAP 3.xlsm").Activate
                    Sheets("8 projects").Visible = False
                    ActiveWorkbook.Protect "planning", Structure:=False, Windows:=False
                    ActiveWorkbook.KeepChangeHistory = True
                    ActiveWorkbook.SaveAs Filename:= _
        "H:\Group\R&D\CP&I RD158\Develop\General\Technical Resource Planning - input files\CAP 3.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, AccessMode:=xlShared
                    Windows("Reporting file - Central Product.xlsm").Activate
                    Range("B2").Select


                    ' Packaging


                    Windows("Packaging.xlsm").Activate
                    If ActiveWorkbook.MultiUserEditing Then
                        ActiveWorkbook.ExclusiveAccess
                    End If
                    ActiveWorkbook.Unprotect "planning"
                    Sheets("8 projects").Visible = True
                    Sheets("8 projects").Select
                    Range("A2:E145").Select
                    Selection.Copy
                    Windows("Reporting file - Central Product.xlsm").Activate
                    Sheets("8 projects").Select
                    Range("A50:E193").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    Windows("Packaging.xlsm").Activate
                    Sheets("8 projects").Visible = False
                    ActiveWorkbook.Protect "planning", Structure:=False, Windows:=False
                    ActiveWorkbook.KeepChangeHistory = True
                    ActiveWorkbook.SaveAs Filename:= _
        "H:\Group\R&D\CP&I RD158\Develop\General\Technical Resource Planning - input files\Packaging.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, AccessMode:=xlShared




                    Windows("Reporting file - Central Product.xlsm").Activate
                    Range("B2").Select
                    ' Product Development


                    Windows("Product development.xlsm").Activate
                    If ActiveWorkbook.MultiUserEditing Then
                        ActiveWorkbook.ExclusiveAccess
                    End If
                    ActiveWorkbook.Unprotect "planning"
                    Sheets("8 projects").Visible = True
                    Sheets("8 projects").Select
                    Range("A2:E209").Select
                    Selection.Copy
                    Windows("Reporting file - Central Product.xlsm").Activate
                    Sheets("8 projects").Select
                    Range("A194:E401").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
                    Windows("Product Development.xlsm").Activate
                    Sheets("8 projects").Visible = False
                    ActiveWorkbook.Protect "planning", Structure:=False, Windows:=False
                    ActiveWorkbook.KeepChangeHistory = True
                    ActiveWorkbook.SaveAs Filename:= _
        "H:\Group\R&D\CP&I RD158\Develop\General\Technical Resource Planning - input files\Product development.xlsm" _
        , FileFormat:=xlOpenXMLWorkbookMacroEnabled, AccessMode:=xlShared
                    Windows("Reporting file - Central Product.xlsm").Activate
                    Range("B2").Select
                    
                    
                    ' Sorting
                        ActiveWorkbook.Worksheets("8 projects").AutoFilter.Sort.SortFields.Clear
                        ActiveWorkbook.Worksheets("8 projects").AutoFilter.Sort.SortFields.Add Key:= _
                            Range("B1:B401"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
                            :=xlSortNormal
                        With ActiveWorkbook.Worksheets("8 projects").AutoFilter.Sort
                            .Header = xlYes
                            .MatchCase = False
                            .Orientation = xlTopToBottom
                            .SortMethod = xlPinYin
                            .Apply
                        End With
 Application.DisplayAlerts = True
' Close windows
            Application.DisplayAlerts = False
            Windows("Master File.xlsm").Close
            Windows("Product development.xlsm").Close
            Windows("Packaging.xlsm").Close
            Windows("CAP 3.xlsm").Close
            Application.DisplayAlerts = True
            
    Application.Calculation = xlManual
    
' update Projects
     Sheets("Projects").Select
     Range("A3:K15000").Select
     Selection.ClearContents
     
     Sheets("Pr Develop").Select
     Range("A3:I4999").Select
     Selection.Copy
     Sheets("Projects").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
     Sheets("Pr CAP 3").Select
     Range("A3:I4999").Select
     Selection.Copy
     Sheets("Projects").Select
    Range("A3").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
        Sheets("Pr Pack").Select
     Range("A3:I4999").Select
     Selection.Copy
     Sheets("Projects").Select
    Range("A3").Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
            Application.Calculation = xlAutomatic
    Sheets("Projects").Select
    Range("L3:AS3").Select
    lRw = Range("A" & Rows.Count).End(xlUp).Row
    Range("L3:AS3", "L" & lRw).FillDown
    
    ActiveSheet.Range("$A$3:$I$14996").removeduplicates Columns:=1, Header:= _
        xlNo
    
' update New Projects
   
    Sheets("New Projects").Select
    Range("A2:BZ10000").Select
    Selection.ClearContents
    Sheets("Master").Select
    Range("A2:BV2548").Select
    Selection.Copy
    Sheets("New Projects").Select
    Range("A2").Select
    ActiveSheet.Paste
     ActiveSheet.Range("$A$1:$AI$10000").AutoFilter Field:=35, Criteria1:="Yes"
    Range("A1").Select
    ActiveSheet.Range(Cells(2, 1), Cells(1000, 35)).Select
    Selection.ClearContents
     ActiveSheet.Range("$A$1:$AI$10000").AutoFilter Field:=35, Criteria1:="No"
    
  ActiveWorkbook.Worksheets("Master").Sort.SortFields.Add Key:=Range("A2:A10000") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Master").Sort
        .SetRange Range("A1:AI10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
' Update projects timeline review and past to dashboard


    Sheets("Projects timeline review").Select
    ActiveSheet.Range("$A$1:$BC$10000").AutoFilter Field:=55
    
    Sheets("ProjectsCalc").Select
    Range("A3:BC10000").Select
    Selection.Copy
    Sheets("Projects timeline review").Select
    Range("A3").Select
    
    
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    ActiveSheet.Range("$A$1:$BC$10000").AutoFilter Field:=55, Criteria1:="="
    Range("A2").Select
    ActiveSheet.Range(Cells(3, 1), Cells(10000, 55)).Select
    Selection.ClearContents
    ActiveSheet.Range("$A$1:$BC$10000").AutoFilter Field:=55


        ActiveWorkbook.Worksheets("Projects timeline review").Sort.SortFields.Add Key _
        :=Range("BC3:BC10000"), SortOn:=xlSortOnValues, Order:=xlDescending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Projects timeline review").Sort
        .SetRange Range("A3:BC10000")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveSheet.Range("$A$1:$BC$10000").AutoFilter Field:=55, Criteria1:="<>"
    
' Hide sheets
    Sheets("Projects").Visible = False
    Sheets("Master").Visible = False
    Sheets("ProjectsCalc").Visible = False
    Sheets("CP import").Visible = False


    Application.ScreenUpdating = True
    


    
    Sheets("DemVsRes").Select
End Sub

Any Ideas what may be the problem?
 
Upvote 0
What is the exact error code and message it gives you (those details are often very important)?
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
VBA in Office 365 and Windows 11
2
Replies
10
Views
533
jkpieterse
jkpieterse
D
  • Question
Excel Office 365/ Excel Linked workbooks on network drive.
Replies
0
Views
213
dcovert
D
J
  • Question
New Laptop - VBA Script will not run using VBS file manually or via Task Scheduler
Replies
0
Views
75
jaihawk8
J
6StringJazzer
  • Question
Constantly getting "Out of memory" messages when VBA window is open
Replies
7
Views
312
6StringJazzer
6StringJazzer
O
  • Question
Send automated emails from Excel Online / Office 365
Replies
0
Views
460
OC2PS
O

Forum statistics

Threads
1,216,182
Messages
6,129,369
Members
449,506
Latest member
nomvula

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top