Karen Schaefer
asked on
open excel workbook vba
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
Why are you closing and reopening the workbook? There may be alternatives to eliminate this requirement.
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thanks again
Pleased to help