How to end Excel Process

D

dreamyed

Im having difficulty ending an excel process, when everything is run, the
process is still running in task manager. I think its got something to do
with me adding charts in my code as it ends the process before this point.

Can anyone help

Heres my code

Private Sub WD5_Report_Click()
On Error GoTo Err_WD5_Report_Click

Dim wd5datecheck, WD5datecheckpre As Date
Dim WD5monthcheck, WD5monthcheckpre, WD5path, WD5path2 As String

wd5datecheck = DLookup("[Date]", "[datemax master]")
WD5datecheckpre = DateAdd("m", -1, DLookup("[Date]", "[datemax master]"))
WD5monthcheck = IIf(Mid(wd5datecheck, 4, 2) = "01", "January",
IIf(Mid(wd5datecheck, 4, 2) = "02", "February", IIf(Mid(wd5datecheck, 4, 2) =
"03", "March", IIf(Mid(wd5datecheck, 4, 2) = "04", "April",
IIf(Mid(wd5datecheck, 4, 2) = "05", "May", IIf(Mid(wd5datecheck, 4, 2) =
"06", "June", IIf(Mid(wd5datecheck, 4, 2) = "07", "July",
IIf(Mid(wd5datecheck, 4, 2) = "08", "August", IIf(Mid(wd5datecheck, 4, 2) =
"09", "September", IIf(Mid(wd5datecheck, 4, 2) = "10", "October",
IIf(Mid(wd5datecheck, 4, 2) = "11", "November", IIf(Mid(wd5datecheck, 4, 2) =
"12", "December", "")))))))))))) & " " & Right(wd5datecheck, 4)
WD5monthcheckpre = IIf(Mid(WD5datecheckpre, 4, 2) = "01", "January",
IIf(Mid(WD5datecheckpre, 4, 2) = "02", "February", IIf(Mid(WD5datecheckpre,
4, 2) = "03", "March", IIf(Mid(WD5datecheckpre, 4, 2) = "04", "April",
IIf(Mid(WD5datecheckpre, 4, 2) = "05", "May", IIf(Mid(WD5datecheckpre, 4, 2)
= "06", "June", IIf(Mid(WD5datecheckpre, 4, 2) = "07", "July",
IIf(Mid(WD5datecheckpre, 4, 2) = "08", "August", IIf(Mid(WD5datecheckpre, 4,
2) = "09", "September", IIf(Mid(WD5datecheckpre, 4, 2) = "10", "October",
IIf(Mid(WD5datecheckpre, 4, 2) = "11", "November", IIf(Mid(WD5datecheckpre,
4, 2) = "12", "December", "")))))))))))) & " " & Right(WD5datecheckpre, 4)
WD5path = "\\scorpio\finance\Finance\Revenue Assurance\Revenue Assurance
Team\Revenue Assurance Performance Matrix\Performance Measurement Reports\"
WD5path2 = "\\scorpio\finance\Finance\Revenue Assurance\Revenue Assurance
Team\Revenue Assurance Performance Matrix\Performance Measurement Reports\"

DoCmd.SetWarnings False

'WD5 Extract

Dim mytable, tempsql As Recordset
Dim myfile, mynewfile, myfilename, myfilenamepre, myfilename2,
mynewfilename, myimpdate, myimpmonth, tempdate, Extractedby, WD5type As String
Dim pth1, pth2, book, sheet, fnday, fnmth, fnmthtemp, filename As String
Dim mytempimpmonth, DateExtracted, WD5datadate As Date
Dim mydb, dblocal As Database
Dim a, n, wsno, i, WD5Day, CellRef As Integer
Dim strfilename As Variant

Dim chartrange As Integer
chartrange = Left(DLookup("[date]", "[datemax master]"), 2) + 1

Set mydb = CurrentDb

myfilenamepre = WD5path & "WD5 Month End Report.xls"
myfilename = WD5path & "WD5 Month End Report.xls"
myfilename2 = WD5path2 & "WD5 Month End Report.xls"
tempdate = Date
filename = myfilename

'This is the bit that modifies the excel spreadsheet to a usable format

strfilename = myfilename

Dim appexcel As Object
' Dim wbknew As Excel.Workbook
' Dim wksnew As Excel.Worksheet

Set appexcel = CreateObject("Excel.Application")

Set dblocal = CurrentDb()
book = strfilename
pth1 = book

appexcel.Visible = True
appexcel.DisplayAlerts = False
appexcel.Workbooks.OpenText filename:=pth1, Origin:=xlMSDOS,
StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True

'Title
appexcel.Sheets("Title").Select

appexcel.Range("A16").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"), "yy")

'Update
appexcel.Sheets("Update").Select

appexcel.Range("A3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " Update"

'Trends
appexcel.Sheets("Trends").Select

appexcel.Range("A1").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " Trends"



'Nav1 Voice Actuals agains Forecast
Set tempsql = CurrentDb.OpenRecordset("select * from [WD5 Legacy Switch
Graph]", dbOpenSnapshot)

'WD5datadate = tempsql![Date]
'DateExtracted = format(Date, "dd/mm/yyyy")
'Extractedby = GetUser

appexcel.Sheets("charts data").Select
CellRef = 1

Do While Not tempsql.EOF
CellRef = CellRef + 1
appexcel.Range("A" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Datex]
appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Sent]
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Forecast]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Date]

tempsql.MoveNext
Loop

appexcel.Sheets("nav1").Select
appexcel.Range("e3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yyyy")

appexcel.Range("f30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Forecast"
appexcel.Range("I30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Succesful Routes sent on O2"

tempsql.Close

appexcel.ActiveSheet.ChartObjects(1).Activate
appexcel.ActiveChart.ChartArea.Select
appexcel.ActiveWindow.Visible = False
appexcel.Selection.Delete

appexcel.Range("a5").Select

appexcel.Charts.Add
appexcel.ActiveChart.ChartType = xlLineMarkers
appexcel.ActiveChart.SetSourceData Source:=Sheets("charts
data").Range("a2:c" & chartrange & ""), _
PlotBy:=xlColumns
' appexcel.ActiveChart.name = "chart Nav1"
appexcel.ActiveChart.Location Where:=xlLocationAsObject, name:="Nav1"

With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

appexcel.ActiveChart.HasLegend = False

With ActiveChart.Parent
.Height = 385 ' resize
.Width = 650 ' resize
.Top = 100 ' reposition
.Left = 50 ' reposition
End With

appexcel.ActiveChart.Axes(xlValue).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
appexcel.ActiveChart.Axes(xlCategory).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With



'Nav3 SMS actuals against forecast
Set tempsql = CurrentDb.OpenRecordset("select * from [WD5 SMSC Switch
Graph]", dbOpenSnapshot)

appexcel.Sheets("charts data").Select
CellRef = 1

Do While Not tempsql.EOF
CellRef = CellRef + 1
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Datex]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Sent]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Forecast]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Date]

tempsql.MoveNext
Loop
tempsql.Close

appexcel.Sheets("nav3").Select
appexcel.Range("e3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yyyy")

appexcel.Range("f30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Forecast"
appexcel.Range("I30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]", "[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Succesful Routes sent on O2"

appexcel.ActiveSheet.ChartObjects(1).Activate
appexcel.ActiveChart.ChartArea.Select
appexcel.ActiveWindow.Visible = False
appexcel.Selection.Delete

appexcel.Range("a5").Select


appexcel.Charts.Add
appexcel.ActiveChart.ChartType = xlLineMarkers
appexcel.ActiveChart.SetSourceData Source:=Sheets("charts
data").Range("E2:G" & chartrange & ""), _
PlotBy:=xlColumns
appexcel.ActiveChart.name = "chart Nev3"
appexcel.ActiveChart.Location Where:=xlLocationAsObject, name:="Nav3"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
appexcel.ActiveChart.HasLegend = False

With ActiveChart.Parent
.Height = 385 ' resize
.Width = 650 ' resize
.Top = 100 ' reposition
.Left = 50 ' reposition
End With

appexcel.ActiveChart.Axes(xlValue).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
appexcel.ActiveChart.Axes(xlCategory).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With




'Network Activity
Set tempsql = CurrentDb.OpenRecordset("select * from [qry smsc switch
network activity]", dbOpenSnapshot)

appexcel.Sheets("Network Activity").Select
CellRef = 3

Do While Not tempsql.EOF
CellRef = CellRef + 1

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![switch Date]
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Total]

tempsql.MoveNext
Loop

tempsql.Close

Set tempsql = CurrentDb.OpenRecordset("select * from [qry smsc switch
network activity prevmonth]", dbOpenSnapshot)

appexcel.Range("B35").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![next month/year] & " Total"

CellRef = 37

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![month/year] & " Total"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofSMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofTotal]

tempsql.Close

Set tempsql = CurrentDb.OpenRecordset("select * from [Qry SMSC Switch
Network Activity Diff Current/Prev]", dbOpenSnapshot)

CellRef = 44

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![month/year] & "
Composition"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp Total]

CellRef = 45

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![prev month/year] & "
Composition"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev Total]

CellRef = 46

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![prev 2 month/year] & "
Composition"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 Total]

CellRef = 47

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![prev 3 month/year] & "
Composition"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 Total]

tempsql.Close

appexcel.ActiveWorkbook.Save
appexcel.ActiveWorkbook.Close
appexcel.Workbooks.Close
appexcel.Application.Quit
Set appexcel = Nothing
book = Null
pth1 = Null




Application.Echo True, "Finished Exporting WD5 Data"
Forms!ReportingMain!Text277.Requery
Forms!ReportingMain!List279.Requery

DoCmd.Close

Exit_WD5_Report_Click:
Exit Sub

Err_WD5_Report_Click:
MsgBox Err.Description
Resume Exit_WD5_Report_Click

End Sub
 
A

Alex Dybenko

Hi,
perhaps this will help:
http://www.mvps.org/access/api/api0025.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

dreamyed said:
Im having difficulty ending an excel process, when everything is run, the
process is still running in task manager. I think its got something to do
with me adding charts in my code as it ends the process before this point.

Can anyone help

Heres my code

Private Sub WD5_Report_Click()
On Error GoTo Err_WD5_Report_Click

Dim wd5datecheck, WD5datecheckpre As Date
Dim WD5monthcheck, WD5monthcheckpre, WD5path, WD5path2 As String

wd5datecheck = DLookup("[Date]", "[datemax master]")
WD5datecheckpre = DateAdd("m", -1, DLookup("[Date]", "[datemax master]"))
WD5monthcheck = IIf(Mid(wd5datecheck, 4, 2) = "01", "January",
IIf(Mid(wd5datecheck, 4, 2) = "02", "February", IIf(Mid(wd5datecheck, 4,
2) =
"03", "March", IIf(Mid(wd5datecheck, 4, 2) = "04", "April",
IIf(Mid(wd5datecheck, 4, 2) = "05", "May", IIf(Mid(wd5datecheck, 4, 2) =
"06", "June", IIf(Mid(wd5datecheck, 4, 2) = "07", "July",
IIf(Mid(wd5datecheck, 4, 2) = "08", "August", IIf(Mid(wd5datecheck, 4, 2)
=
"09", "September", IIf(Mid(wd5datecheck, 4, 2) = "10", "October",
IIf(Mid(wd5datecheck, 4, 2) = "11", "November", IIf(Mid(wd5datecheck, 4,
2) =
"12", "December", "")))))))))))) & " " & Right(wd5datecheck, 4)
WD5monthcheckpre = IIf(Mid(WD5datecheckpre, 4, 2) = "01", "January",
IIf(Mid(WD5datecheckpre, 4, 2) = "02", "February",
IIf(Mid(WD5datecheckpre,
4, 2) = "03", "March", IIf(Mid(WD5datecheckpre, 4, 2) = "04", "April",
IIf(Mid(WD5datecheckpre, 4, 2) = "05", "May", IIf(Mid(WD5datecheckpre, 4,
2)
= "06", "June", IIf(Mid(WD5datecheckpre, 4, 2) = "07", "July",
IIf(Mid(WD5datecheckpre, 4, 2) = "08", "August", IIf(Mid(WD5datecheckpre,
4,
2) = "09", "September", IIf(Mid(WD5datecheckpre, 4, 2) = "10", "October",
IIf(Mid(WD5datecheckpre, 4, 2) = "11", "November",
IIf(Mid(WD5datecheckpre,
4, 2) = "12", "December", "")))))))))))) & " " & Right(WD5datecheckpre, 4)
WD5path = "\\scorpio\finance\Finance\Revenue Assurance\Revenue Assurance
Team\Revenue Assurance Performance Matrix\Performance Measurement
Reports\"
WD5path2 = "\\scorpio\finance\Finance\Revenue Assurance\Revenue Assurance
Team\Revenue Assurance Performance Matrix\Performance Measurement
Reports\"

DoCmd.SetWarnings False

'WD5 Extract

Dim mytable, tempsql As Recordset
Dim myfile, mynewfile, myfilename, myfilenamepre, myfilename2,
mynewfilename, myimpdate, myimpmonth, tempdate, Extractedby, WD5type As
String
Dim pth1, pth2, book, sheet, fnday, fnmth, fnmthtemp, filename As
String
Dim mytempimpmonth, DateExtracted, WD5datadate As Date
Dim mydb, dblocal As Database
Dim a, n, wsno, i, WD5Day, CellRef As Integer
Dim strfilename As Variant

Dim chartrange As Integer
chartrange = Left(DLookup("[date]", "[datemax master]"), 2) + 1

Set mydb = CurrentDb

myfilenamepre = WD5path & "WD5 Month End Report.xls"
myfilename = WD5path & "WD5 Month End Report.xls"
myfilename2 = WD5path2 & "WD5 Month End Report.xls"
tempdate = Date
filename = myfilename

'This is the bit that modifies the excel spreadsheet to a usable format

strfilename = myfilename

Dim appexcel As Object
' Dim wbknew As Excel.Workbook
' Dim wksnew As Excel.Worksheet

Set appexcel = CreateObject("Excel.Application")

Set dblocal = CurrentDb()
book = strfilename
pth1 = book

appexcel.Visible = True
appexcel.DisplayAlerts = False
appexcel.Workbooks.OpenText filename:=pth1, Origin:=xlMSDOS,
StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True

'Title
appexcel.Sheets("Title").Select

appexcel.Range("A16").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy")

'Update
appexcel.Sheets("Update").Select

appexcel.Range("A3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " Update"

'Trends
appexcel.Sheets("Trends").Select

appexcel.Range("A1").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " Trends"



'Nav1 Voice Actuals agains Forecast
Set tempsql = CurrentDb.OpenRecordset("select * from [WD5 Legacy Switch
Graph]", dbOpenSnapshot)

'WD5datadate = tempsql![Date]
'DateExtracted = format(Date, "dd/mm/yyyy")
'Extractedby = GetUser

appexcel.Sheets("charts data").Select
CellRef = 1

Do While Not tempsql.EOF
CellRef = CellRef + 1
appexcel.Range("A" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Datex]
appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Sent]
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Forecast]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Date]

tempsql.MoveNext
Loop

appexcel.Sheets("nav1").Select
appexcel.Range("e3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yyyy")

appexcel.Range("f30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Forecast"
appexcel.Range("I30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Succesful Routes sent on O2"

tempsql.Close

appexcel.ActiveSheet.ChartObjects(1).Activate
appexcel.ActiveChart.ChartArea.Select
appexcel.ActiveWindow.Visible = False
appexcel.Selection.Delete

appexcel.Range("a5").Select

appexcel.Charts.Add
appexcel.ActiveChart.ChartType = xlLineMarkers
appexcel.ActiveChart.SetSourceData Source:=Sheets("charts
data").Range("a2:c" & chartrange & ""), _
PlotBy:=xlColumns
' appexcel.ActiveChart.name = "chart Nav1"
appexcel.ActiveChart.Location Where:=xlLocationAsObject, name:="Nav1"

With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

appexcel.ActiveChart.HasLegend = False

With ActiveChart.Parent
.Height = 385 ' resize
.Width = 650 ' resize
.Top = 100 ' reposition
.Left = 50 ' reposition
End With

appexcel.ActiveChart.Axes(xlValue).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
appexcel.ActiveChart.Axes(xlCategory).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With



'Nav3 SMS actuals against forecast
Set tempsql = CurrentDb.OpenRecordset("select * from [WD5 SMSC Switch
Graph]", dbOpenSnapshot)

appexcel.Sheets("charts data").Select
CellRef = 1

Do While Not tempsql.EOF
CellRef = CellRef + 1
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Datex]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Sent]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Forecast]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Date]

tempsql.MoveNext
Loop
tempsql.Close

appexcel.Sheets("nav3").Select
appexcel.Range("e3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yyyy")

appexcel.Range("f30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Forecast"
appexcel.Range("I30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Succesful Routes sent on O2"

appexcel.ActiveSheet.ChartObjects(1).Activate
appexcel.ActiveChart.ChartArea.Select
appexcel.ActiveWindow.Visible = False
appexcel.Selection.Delete

appexcel.Range("a5").Select


appexcel.Charts.Add
appexcel.ActiveChart.ChartType = xlLineMarkers
appexcel.ActiveChart.SetSourceData Source:=Sheets("charts
data").Range("E2:G" & chartrange & ""), _
PlotBy:=xlColumns
appexcel.ActiveChart.name = "chart Nev3"
appexcel.ActiveChart.Location Where:=xlLocationAsObject, name:="Nav3"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
appexcel.ActiveChart.HasLegend = False

With ActiveChart.Parent
.Height = 385 ' resize
.Width = 650 ' resize
.Top = 100 ' reposition
.Left = 50 ' reposition
End With

appexcel.ActiveChart.Axes(xlValue).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
appexcel.ActiveChart.Axes(xlCategory).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With




'Network Activity
Set tempsql = CurrentDb.OpenRecordset("select * from [qry smsc switch
network activity]", dbOpenSnapshot)

appexcel.Sheets("Network Activity").Select
CellRef = 3

Do While Not tempsql.EOF
CellRef = CellRef + 1

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![switch Date]
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Total]

tempsql.MoveNext
Loop

tempsql.Close

Set tempsql = CurrentDb.OpenRecordset("select * from [qry smsc switch
network activity prevmonth]", dbOpenSnapshot)

appexcel.Range("B35").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![next month/year] & "
Total"

CellRef = 37

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![month/year] & " Total"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofMT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofSMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![sumofTotal]

tempsql.Close

Set tempsql = CurrentDb.OpenRecordset("select * from [Qry SMSC Switch
Network Activity Diff Current/Prev]", dbOpenSnapshot)

CellRef = 44

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![month/year] & "
Composition"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp Total]

CellRef = 45

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![prev month/year] & "
Composition"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev Total]

CellRef = 46

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![prev 2 month/year] & "
Composition"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 2 Total]

CellRef = 47

appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![prev 3 month/year] & "
Composition"
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MO P2P]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MO M2L]
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MO PRM]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MO UNK]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MT PRM]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MT STD]
appexcel.Range("I" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 MT UNK]
appexcel.Range("J" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 SMSRTotal]
appexcel.Range("K" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![comp prev 3 Total]

tempsql.Close

appexcel.ActiveWorkbook.Save
appexcel.ActiveWorkbook.Close
appexcel.Workbooks.Close
appexcel.Application.Quit
Set appexcel = Nothing
book = Null
pth1 = Null




Application.Echo True, "Finished Exporting WD5 Data"
Forms!ReportingMain!Text277.Requery
Forms!ReportingMain!List279.Requery

DoCmd.Close

Exit_WD5_Report_Click:
Exit Sub

Err_WD5_Report_Click:
MsgBox Err.Description
Resume Exit_WD5_Report_Click

End Sub
Click to expand...
 
D

dreamyed

Hi, thanks for replying, unfortunately this doesnt seem to work. Do you have
any other ideas?



Alex Dybenko said:
Hi,
perhaps this will help:
http://www.mvps.org/access/api/api0025.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

dreamyed said:
Im having difficulty ending an excel process, when everything is run, the
process is still running in task manager. I think its got something to do
with me adding charts in my code as it ends the process before this point.

Can anyone help

Heres my code

Private Sub WD5_Report_Click()
On Error GoTo Err_WD5_Report_Click

Dim wd5datecheck, WD5datecheckpre As Date
Dim WD5monthcheck, WD5monthcheckpre, WD5path, WD5path2 As String

wd5datecheck = DLookup("[Date]", "[datemax master]")
WD5datecheckpre = DateAdd("m", -1, DLookup("[Date]", "[datemax master]"))
WD5monthcheck = IIf(Mid(wd5datecheck, 4, 2) = "01", "January",
IIf(Mid(wd5datecheck, 4, 2) = "02", "February", IIf(Mid(wd5datecheck, 4,
2) =
"03", "March", IIf(Mid(wd5datecheck, 4, 2) = "04", "April",
IIf(Mid(wd5datecheck, 4, 2) = "05", "May", IIf(Mid(wd5datecheck, 4, 2) =
"06", "June", IIf(Mid(wd5datecheck, 4, 2) = "07", "July",
IIf(Mid(wd5datecheck, 4, 2) = "08", "August", IIf(Mid(wd5datecheck, 4, 2)
=
"09", "September", IIf(Mid(wd5datecheck, 4, 2) = "10", "October",
IIf(Mid(wd5datecheck, 4, 2) = "11", "November", IIf(Mid(wd5datecheck, 4,
2) =
"12", "December", "")))))))))))) & " " & Right(wd5datecheck, 4)
WD5monthcheckpre = IIf(Mid(WD5datecheckpre, 4, 2) = "01", "January",
IIf(Mid(WD5datecheckpre, 4, 2) = "02", "February",
IIf(Mid(WD5datecheckpre,
4, 2) = "03", "March", IIf(Mid(WD5datecheckpre, 4, 2) = "04", "April",
IIf(Mid(WD5datecheckpre, 4, 2) = "05", "May", IIf(Mid(WD5datecheckpre, 4,
2)
= "06", "June", IIf(Mid(WD5datecheckpre, 4, 2) = "07", "July",
IIf(Mid(WD5datecheckpre, 4, 2) = "08", "August", IIf(Mid(WD5datecheckpre,
4,
2) = "09", "September", IIf(Mid(WD5datecheckpre, 4, 2) = "10", "October",
IIf(Mid(WD5datecheckpre, 4, 2) = "11", "November",
IIf(Mid(WD5datecheckpre,
4, 2) = "12", "December", "")))))))))))) & " " & Right(WD5datecheckpre, 4)
WD5path = "\\scorpio\finance\Finance\Revenue Assurance\Revenue Assurance
Team\Revenue Assurance Performance Matrix\Performance Measurement
Reports\"
WD5path2 = "\\scorpio\finance\Finance\Revenue Assurance\Revenue Assurance
Team\Revenue Assurance Performance Matrix\Performance Measurement
Reports\"

DoCmd.SetWarnings False

'WD5 Extract

Dim mytable, tempsql As Recordset
Dim myfile, mynewfile, myfilename, myfilenamepre, myfilename2,
mynewfilename, myimpdate, myimpmonth, tempdate, Extractedby, WD5type As
String
Dim pth1, pth2, book, sheet, fnday, fnmth, fnmthtemp, filename As
String
Dim mytempimpmonth, DateExtracted, WD5datadate As Date
Dim mydb, dblocal As Database
Dim a, n, wsno, i, WD5Day, CellRef As Integer
Dim strfilename As Variant

Dim chartrange As Integer
chartrange = Left(DLookup("[date]", "[datemax master]"), 2) + 1

Set mydb = CurrentDb

myfilenamepre = WD5path & "WD5 Month End Report.xls"
myfilename = WD5path & "WD5 Month End Report.xls"
myfilename2 = WD5path2 & "WD5 Month End Report.xls"
tempdate = Date
filename = myfilename

'This is the bit that modifies the excel spreadsheet to a usable format

strfilename = myfilename

Dim appexcel As Object
' Dim wbknew As Excel.Workbook
' Dim wksnew As Excel.Worksheet

Set appexcel = CreateObject("Excel.Application")

Set dblocal = CurrentDb()
book = strfilename
pth1 = book

appexcel.Visible = True
appexcel.DisplayAlerts = False
appexcel.Workbooks.OpenText filename:=pth1, Origin:=xlMSDOS,
StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True

'Title
appexcel.Sheets("Title").Select

appexcel.Range("A16").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy")

'Update
appexcel.Sheets("Update").Select

appexcel.Range("A3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " Update"

'Trends
appexcel.Sheets("Trends").Select

appexcel.Range("A1").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " Trends"



'Nav1 Voice Actuals agains Forecast
Set tempsql = CurrentDb.OpenRecordset("select * from [WD5 Legacy Switch
Graph]", dbOpenSnapshot)

'WD5datadate = tempsql![Date]
'DateExtracted = format(Date, "dd/mm/yyyy")
'Extractedby = GetUser

appexcel.Sheets("charts data").Select
CellRef = 1

Do While Not tempsql.EOF
CellRef = CellRef + 1
appexcel.Range("A" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Datex]
appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Sent]
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Forecast]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Date]

tempsql.MoveNext
Loop

appexcel.Sheets("nav1").Select
appexcel.Range("e3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yyyy")

appexcel.Range("f30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Forecast"
appexcel.Range("I30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Succesful Routes sent on O2"

tempsql.Close

appexcel.ActiveSheet.ChartObjects(1).Activate
appexcel.ActiveChart.ChartArea.Select
appexcel.ActiveWindow.Visible = False
appexcel.Selection.Delete

appexcel.Range("a5").Select

appexcel.Charts.Add
appexcel.ActiveChart.ChartType = xlLineMarkers
appexcel.ActiveChart.SetSourceData Source:=Sheets("charts
data").Range("a2:c" & chartrange & ""), _
PlotBy:=xlColumns
' appexcel.ActiveChart.name = "chart Nav1"
appexcel.ActiveChart.Location Where:=xlLocationAsObject, name:="Nav1"

With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

appexcel.ActiveChart.HasLegend = False

With ActiveChart.Parent
.Height = 385 ' resize
.Width = 650 ' resize
.Top = 100 ' reposition
.Left = 50 ' reposition
End With

appexcel.ActiveChart.Axes(xlValue).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
appexcel.ActiveChart.Axes(xlCategory).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With



'Nav3 SMS actuals against forecast
Set tempsql = CurrentDb.OpenRecordset("select * from [WD5 SMSC Switch
Graph]", dbOpenSnapshot)

appexcel.Sheets("charts data").Select
CellRef = 1

Do While Not tempsql.EOF
CellRef = CellRef + 1
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Datex]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Sent]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Forecast]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Date]

tempsql.MoveNext
Loop
tempsql.Close

appexcel.Sheets("nav3").Select
appexcel.Range("e3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yyyy")

appexcel.Range("f30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Forecast"
appexcel.Range("I30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Succesful Routes sent on O2"

appexcel.ActiveSheet.ChartObjects(1).Activate
appexcel.ActiveChart.ChartArea.Select
appexcel.ActiveWindow.Visible = False
appexcel.Selection.Delete

appexcel.Range("a5").Select


appexcel.Charts.Add
appexcel.ActiveChart.ChartType = xlLineMarkers
appexcel.ActiveChart.SetSourceData Source:=Sheets("charts
data").Range("E2:G" & chartrange & ""), _
PlotBy:=xlColumns
appexcel.ActiveChart.name = "chart Nev3"
Click to expand...
Click to expand...
 
D

dreamyed

Sorry should have expanded, excel isnt open anymore the actual program closes
down, but in task manager under processes, it is still open. When you try and
re-open excel it part opens and the pane freezes. If you try and re-run the
code it errors. I need a way of shutting down the process in task manager.

Alex Dybenko said:
Hi,
perhaps this will help:
http://www.mvps.org/access/api/api0025.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

dreamyed said:
Im having difficulty ending an excel process, when everything is run, the
process is still running in task manager. I think its got something to do
with me adding charts in my code as it ends the process before this point.

Can anyone help

Heres my code

Private Sub WD5_Report_Click()
On Error GoTo Err_WD5_Report_Click

Dim wd5datecheck, WD5datecheckpre As Date
Dim WD5monthcheck, WD5monthcheckpre, WD5path, WD5path2 As String

wd5datecheck = DLookup("[Date]", "[datemax master]")
WD5datecheckpre = DateAdd("m", -1, DLookup("[Date]", "[datemax master]"))
WD5monthcheck = IIf(Mid(wd5datecheck, 4, 2) = "01", "January",
IIf(Mid(wd5datecheck, 4, 2) = "02", "February", IIf(Mid(wd5datecheck, 4,
2) =
"03", "March", IIf(Mid(wd5datecheck, 4, 2) = "04", "April",
IIf(Mid(wd5datecheck, 4, 2) = "05", "May", IIf(Mid(wd5datecheck, 4, 2) =
"06", "June", IIf(Mid(wd5datecheck, 4, 2) = "07", "July",
IIf(Mid(wd5datecheck, 4, 2) = "08", "August", IIf(Mid(wd5datecheck, 4, 2)
=
"09", "September", IIf(Mid(wd5datecheck, 4, 2) = "10", "October",
IIf(Mid(wd5datecheck, 4, 2) = "11", "November", IIf(Mid(wd5datecheck, 4,
2) =
"12", "December", "")))))))))))) & " " & Right(wd5datecheck, 4)
WD5monthcheckpre = IIf(Mid(WD5datecheckpre, 4, 2) = "01", "January",
IIf(Mid(WD5datecheckpre, 4, 2) = "02", "February",
IIf(Mid(WD5datecheckpre,
4, 2) = "03", "March", IIf(Mid(WD5datecheckpre, 4, 2) = "04", "April",
IIf(Mid(WD5datecheckpre, 4, 2) = "05", "May", IIf(Mid(WD5datecheckpre, 4,
2)
= "06", "June", IIf(Mid(WD5datecheckpre, 4, 2) = "07", "July",
IIf(Mid(WD5datecheckpre, 4, 2) = "08", "August", IIf(Mid(WD5datecheckpre,
4,
2) = "09", "September", IIf(Mid(WD5datecheckpre, 4, 2) = "10", "October",
IIf(Mid(WD5datecheckpre, 4, 2) = "11", "November",
IIf(Mid(WD5datecheckpre,
4, 2) = "12", "December", "")))))))))))) & " " & Right(WD5datecheckpre, 4)
WD5path = "\\scorpio\finance\Finance\Revenue Assurance\Revenue Assurance
Team\Revenue Assurance Performance Matrix\Performance Measurement
Reports\"
WD5path2 = "\\scorpio\finance\Finance\Revenue Assurance\Revenue Assurance
Team\Revenue Assurance Performance Matrix\Performance Measurement
Reports\"

DoCmd.SetWarnings False

'WD5 Extract

Dim mytable, tempsql As Recordset
Dim myfile, mynewfile, myfilename, myfilenamepre, myfilename2,
mynewfilename, myimpdate, myimpmonth, tempdate, Extractedby, WD5type As
String
Dim pth1, pth2, book, sheet, fnday, fnmth, fnmthtemp, filename As
String
Dim mytempimpmonth, DateExtracted, WD5datadate As Date
Dim mydb, dblocal As Database
Dim a, n, wsno, i, WD5Day, CellRef As Integer
Dim strfilename As Variant

Dim chartrange As Integer
chartrange = Left(DLookup("[date]", "[datemax master]"), 2) + 1

Set mydb = CurrentDb

myfilenamepre = WD5path & "WD5 Month End Report.xls"
myfilename = WD5path & "WD5 Month End Report.xls"
myfilename2 = WD5path2 & "WD5 Month End Report.xls"
tempdate = Date
filename = myfilename

'This is the bit that modifies the excel spreadsheet to a usable format

strfilename = myfilename

Dim appexcel As Object
' Dim wbknew As Excel.Workbook
' Dim wksnew As Excel.Worksheet

Set appexcel = CreateObject("Excel.Application")

Set dblocal = CurrentDb()
book = strfilename
pth1 = book

appexcel.Visible = True
appexcel.DisplayAlerts = False
appexcel.Workbooks.OpenText filename:=pth1, Origin:=xlMSDOS,
StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, Comma:=True, Space:=False, Other:=False,
FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)),
TrailingMinusNumbers:=True

'Title
appexcel.Sheets("Title").Select

appexcel.Range("A16").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy")

'Update
appexcel.Sheets("Update").Select

appexcel.Range("A3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " Update"

'Trends
appexcel.Sheets("Trends").Select

appexcel.Range("A1").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " Trends"



'Nav1 Voice Actuals agains Forecast
Set tempsql = CurrentDb.OpenRecordset("select * from [WD5 Legacy Switch
Graph]", dbOpenSnapshot)

'WD5datadate = tempsql![Date]
'DateExtracted = format(Date, "dd/mm/yyyy")
'Extractedby = GetUser

appexcel.Sheets("charts data").Select
CellRef = 1

Do While Not tempsql.EOF
CellRef = CellRef + 1
appexcel.Range("A" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Datex]
appexcel.Range("B" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Sent]
appexcel.Range("C" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Forecast]
appexcel.Range("D" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Date]

tempsql.MoveNext
Loop

appexcel.Sheets("nav1").Select
appexcel.Range("e3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yyyy")

appexcel.Range("f30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Forecast"
appexcel.Range("I30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Succesful Routes sent on O2"

tempsql.Close

appexcel.ActiveSheet.ChartObjects(1).Activate
appexcel.ActiveChart.ChartArea.Select
appexcel.ActiveWindow.Visible = False
appexcel.Selection.Delete

appexcel.Range("a5").Select

appexcel.Charts.Add
appexcel.ActiveChart.ChartType = xlLineMarkers
appexcel.ActiveChart.SetSourceData Source:=Sheets("charts
data").Range("a2:c" & chartrange & ""), _
PlotBy:=xlColumns
' appexcel.ActiveChart.name = "chart Nav1"
appexcel.ActiveChart.Location Where:=xlLocationAsObject, name:="Nav1"

With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With

appexcel.ActiveChart.HasLegend = False

With ActiveChart.Parent
.Height = 385 ' resize
.Width = 650 ' resize
.Top = 100 ' reposition
.Left = 50 ' reposition
End With

appexcel.ActiveChart.Axes(xlValue).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With
appexcel.ActiveChart.Axes(xlCategory).Select
appexcel.Selection.TickLabels.AutoScaleFont = True
With Selection.TickLabels.Font
.name = "Arial"
.FontStyle = "Regular"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Background = xlAutomatic
End With



'Nav3 SMS actuals against forecast
Set tempsql = CurrentDb.OpenRecordset("select * from [WD5 SMSC Switch
Graph]", dbOpenSnapshot)

appexcel.Sheets("charts data").Select
CellRef = 1

Do While Not tempsql.EOF
CellRef = CellRef + 1
appexcel.Range("E" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Datex]
appexcel.Range("F" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Sent]
appexcel.Range("G" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Forecast]
appexcel.Range("H" & CellRef & "").Select
appexcel.ActiveCell.FormulaR1C1 = tempsql![Date]

tempsql.MoveNext
Loop
tempsql.Close

appexcel.Sheets("nav3").Select
appexcel.Range("e3").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yyyy")

appexcel.Range("f30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Forecast"
appexcel.Range("I30").Select
appexcel.ActiveCell.FormulaR1C1 = format(DLookup("[date]",
"[datemax
master]"), "mmmm") & " " & format(DLookup("[date]", "[datemax master]"),
"yy") & " Succesful Routes sent on O2"

appexcel.ActiveSheet.ChartObjects(1).Activate
appexcel.ActiveChart.ChartArea.Select
appexcel.ActiveWindow.Visible = False
appexcel.Selection.Delete

appexcel.Range("a5").Select


appexcel.Charts.Add
appexcel.ActiveChart.ChartType = xlLineMarkers
appexcel.ActiveChart.SetSourceData Source:=Sheets("charts
data").Range("E2:G" & chartrange & ""), _
PlotBy:=xlColumns
appexcel.ActiveChart.name = "chart Nev3"
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ideas on how to do Stock Excel File 0
How to Search two column value are match 0
Copy Chart from Excel to Powerpoint Pres 2
How to refresh a record in a Access Active X control... 2
MS Download Create QBF-how to use listbox 10
Very Slow reading excel data into an array (while opened in new xl 17

Top