Poundland
11-14-2016, 05:13 AM
All,
I am baffled as to why the below code, which is part of a larger code stream, works on some occasions and not on others, it is very random.
I will take some time to explain...
The code extract below is part of a larger code project, which all works fine, and works consistently every day without fail.
The code below is doing the same thing 5 times, which is basically opening a blank workbook, copying a tab from another workbook to the new workbook and then saving the workbook..
Now here is the odd thing, when the code fails it will fail on the first copy function, when I skip past this, the code then successfully completes the other 4 copy functions, which basically mirror the first one.
When the blank workbook is opened, on occasion Excel cannot see the workbook despite the Debug,Print function knowing the workbook is there. The Copy function will copy over a create a blank worksheet into the Blank Workbook, but will not copy the required tab into the new workbook.
I have tried all sorts to get Excel to recognize the Blank Workbook, and sometimes it works and other times it doesn't. It is very strange...
I tried creating a new workbook also instead of opening a Blank workbook, but had the same issue, sometimes it worked and other times it didn't.
Can anybody give me any ideas on how I can get this to consistently work.
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
' New Code lins to rectify finding workbook issue
Set wrkMu = GetWorkbook("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
If Not wrkMu Is Nothing Then
Debug.Print wrkMu.Name
End If
' End of new code lines
wrkMu.Activate ' Code line added to try and rectify Workbook selection issue
Windows("Aged Stock - Blank.xlsx").Activate ' Code line added to try and rectify Workbook selection issue
wrkAged.Activate
Windows("Aged Stock - Blank.xlsx").Activate ' Code line added to try and rectify Workbook selection issue
With wrkAged.Sheets("Food Aged")
.Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets(1)
End With
'Sheets("Food Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Food.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Food.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' Non Food
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("Non Food Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Non Food.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Non Food.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' GM
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("GM Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - GM.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - GM.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' Multiprice
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("Multiprice Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Multiprice.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Multiprice.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' All
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("All Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Manager.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Manager.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
I am baffled as to why the below code, which is part of a larger code stream, works on some occasions and not on others, it is very random.
I will take some time to explain...
The code extract below is part of a larger code project, which all works fine, and works consistently every day without fail.
The code below is doing the same thing 5 times, which is basically opening a blank workbook, copying a tab from another workbook to the new workbook and then saving the workbook..
Now here is the odd thing, when the code fails it will fail on the first copy function, when I skip past this, the code then successfully completes the other 4 copy functions, which basically mirror the first one.
When the blank workbook is opened, on occasion Excel cannot see the workbook despite the Debug,Print function knowing the workbook is there. The Copy function will copy over a create a blank worksheet into the Blank Workbook, but will not copy the required tab into the new workbook.
I have tried all sorts to get Excel to recognize the Blank Workbook, and sometimes it works and other times it doesn't. It is very strange...
I tried creating a new workbook also instead of opening a Blank workbook, but had the same issue, sometimes it worked and other times it didn't.
Can anybody give me any ideas on how I can get this to consistently work.
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
' New Code lins to rectify finding workbook issue
Set wrkMu = GetWorkbook("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
If Not wrkMu Is Nothing Then
Debug.Print wrkMu.Name
End If
' End of new code lines
wrkMu.Activate ' Code line added to try and rectify Workbook selection issue
Windows("Aged Stock - Blank.xlsx").Activate ' Code line added to try and rectify Workbook selection issue
wrkAged.Activate
Windows("Aged Stock - Blank.xlsx").Activate ' Code line added to try and rectify Workbook selection issue
With wrkAged.Sheets("Food Aged")
.Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets(1)
End With
'Sheets("Food Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Food.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Food.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' Non Food
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("Non Food Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Non Food.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Non Food.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' GM
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("GM Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - GM.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - GM.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' Multiprice
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("Multiprice Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Multiprice.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Multiprice.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False
' All
Set wrkMu = Excel.Workbooks.Open("I:\H925 Trading Dashboard Reports\Aged Stock - Blank.xlsx")
wrkAged.Activate
Sheets("All Aged").Copy Before:=Workbooks("Aged Stock - Blank.xlsx").Sheets( _
1)
wrkMu.Activate
On Error Resume Next
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Manager.xlsx", Password:="danielle"
wrkMu.SaveAs Filename:="I:\H925 Trading Dashboard Reports\" & Format(pdat, "dd.mm.yyyy") & " Aged Stock - Manager.xlsx", Password:="danielle"
On Error GoTo 0
wrkMu.Close False