Running Code in IE window

[robcarr]

hi,

I use a tool called impact360 to generate excel reports that I work from,when a report is run i choose the option "excel 2000 output", so the file is generate as an excel file.

I have recorded some code with the generated report visible.

Range("B9").Select
    ChDir "U:\Private\Temp\Impact 360 Exports"
    ActiveWorkbook.SaveAs Filename:= _
        "U:\Private\Temp\Impact 360 Exports\13.12.08.xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWorkbook.Close
    ActiveWorkbook.Close
    ActiveWindow.Close

that is all the coding that is generated. this is fairly basic and can be trimmed, this is not what the problem is.

I have saved this in my personal addin as

Dim ddate As Date
If Range("a2").Value = "Planned Activity Cost (Unburdened) " Then
Range("b6").Formula = "=LEFT(TRIM(RIGHT(TRIM(B1),LEN(TRIM(B1))-FIND("": "",TRIM(B1)))),FIND("" "",TRIM(RIGHT(TRIM(B1),LEN(TRIM(B1))-FIND("": "",TRIM(B1)))))-1)"
ddate = Range("b65536").End(xlUp).Value - 1
    Range("A5").Select
    ActiveWorkbook.SaveAs FileName:= _
        "U:\Private\Temp\Impact 360 Exports\" & Format(ddate, "dd.mm.yy") & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWorkbook.Close
     ActiveWorkbook.Close
Else
 ddate = Range("b65536").End(xlUp).Value - 1
 ActiveWorkbook.SaveAs FileName:= _
        "U:\Private\Temp\Impact 360 Exports\adh " & Format(ddate, "dd.mm.yy") & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWorkbook.Close
     ActiveWorkbook.Close
End If

this code is loaded into my right click menu - so i can access it in the ie window, the right click menu appears, but does not do anything/or work.

anyone else done this and got it to work.



Hope this is of use, Rob.

 

[PHV]

You may have to use qualified ranges:

Dim ddate As Date
With ActiveWorkbook
  ddate = .Range("b65536").End(xlUp).Value - 1
  If .Range("a2").Value = "Planned Activity Cost (Unburdened) " Then
    .Range("b6").Formula = "=LEFT(TRIM(RIGHT(TRIM(B1),LEN(TRIM(B1))-FIND("": "",TRIM(B1)))),FIND("" "",TRIM(RIGHT(TRIM(B1),LEN(TRIM(B1))-FIND("": "",TRIM(B1)))))-1)"
    .Range("A5").Select
    .SaveAs FileName:= _
        "U:\Private\Temp\Impact 360 Exports\" & Format(ddate, "dd.mm.yy") & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
  Else
    .SaveAs FileName:= _
        "U:\Private\Temp\Impact 360 Exports\adh " & Format(ddate, "dd.mm.yy") & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
  End If
  .Close
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robcarr]

phv,

thanks for the suggsetion, the code runs and saves as it should, however the window does not close.

it seems excel knows there is an excel file as it lets me use my right click menu, however the window does not display anywhere in excel (window list in excel menu, vba tree etc).

With ActiveWindow
Dim ddate As Date
If Range("a2").Value = "Planned Activity Cost (Unburdened) " Then
Range("b6").NumberFormat = "General"
Range("b6").Formula = "=LEFT(TRIM(RIGHT(TRIM(a6),LEN(TRIM(a6))-FIND("": "",TRIM(a6)))),FIND("" "",TRIM(RIGHT(TRIM(a6),LEN(TRIM(a6))-FIND("": "",TRIM(a6)))))-1)"
ddate = Range("b6").Value
    Range("A5").Select
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName:= _
        "U:\Private\Temp\Impact 360 Exports\" & Format(ddate, "dd.mm.yy") & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
        Application.DisplayAlerts = True
Else
 ddate = Range("c6").Value
 ActiveWorkbook.SaveAs FileName:= _
        "U:\Private\Temp\Impact 360 Exports\adh " & Format(ddate, "dd.mm.yy") & ".xls", FileFormat:=xlNormal, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
End If
.Close
End With
End Sub

is there anyway else to close the instance, as I would prefer not to click the X in the corner each time if possible.

Hope this is of use, Rob.

 

[PHV]

What about Application.Quit ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robcarr]

ah, simple things, hadn't tried that.

one note, when i stepped into the coding "activewindow" came up as nothing



Hope this is of use, Rob.

 

[robcarr]

have tried the application.quit and no joy.

However if i have excel open and run the coding through the IE window it will try and quit excel and not the IE window.

Hope this is of use, Rob.