open excel workbook vba

Karen Schaefer
Karen Schaefer used Ask the Experts™
on
need help with the correct syntax based on the code sample.  I need to close then reopen a workbook after it has been saved.  what is the proper syntax?

            Set myBook = Nothing
            On Error Resume Next
            Set myBook = Workbooks.Open(MyPath & MyFiles(FNum), UpdateLinks:=0)

                    If ShName = "ResPlan_Data" Then
                        Call UnpivotResPlan
                        myBook.Save
                        myBook.Close
                        mybook.????

                     End If
                        

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Why are you closing and reopening the workbook? There may be alternatives to eliminate this requirement.
Karen SchaeferBI ANALYST

Author

Commented:
because I need update the data in one workbook, then save it then access the saved data.  When I tried to use active worksheet.  the data was not copied into the master template, but if I reran the code the saved file was able to update the master template.  So this was my workaround, and I got it work acceptably.

                 If ShName = "ResPlan_Data" Then
                        Call UnpivotResPlan
                        myBook.Close savechanges:=True
                        Set myBook = Workbooks.Open(MyPath & MyFiles(FNum), UpdateLinks:=0)
                        Set ws = myBook.Worksheets(ShName)
                     End If
                        
                        '**************************************************
                        'Updates template data per shName
                        '*************************************************
                        If Not ws Is Nothing Then
                            Set BaseWks = wbMaster.Worksheets(ShName)
                            Set sourceRange = ws.UsedRange
                            'Exclude header labels
                            Set rng = sourceRange.Offset(1, 0).Resize(sourceRange.Rows.Count - 1, sourceRange.Columns.Count)
    
                            RwCount = rng.Rows.Count
                            rnum = BaseWks.Cells(BaseWks.Rows.Count, 1).End(xlUp).Row + 1
    
                            BaseWks.Cells(rnum, "A").Resize(RwCount).Value _
                                  = myBook.Name
                            BaseWks.Cells(rnum, "B").Resize(RwCount, rng.Columns.Count).Value = rng.Value
                        End If
                        With wbMaster.Worksheets(ShName).ListObjects(1)
                            On Error Resume Next
                                .DataBodyRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                            On Error GoTo 0
                        End With
                
            Next ShName

Open in new window

Group Finance Manager
Commented:
If the code is in the workbook that you are closing then it cannot re-open itself. So what is MyBook referring to here

 myBook.Close savechanges:=True

Open in new window

Karen SchaeferBI ANALYST

Author

Commented:
thanks again
Roy CoxGroup Finance Manager

Commented:
Pleased to help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial