Peter T
12/18/2006 3:13:00 PM
> Normally this [load funcress.xla] would work with most addins but it
doesn't seem to with
> Analysis Toolpak, I guess it's associated xll does not correctly load.
This seems to work, tested in Word
Dim xlApp As Object
Sub test()
Dim ad As Object
Set xlApp = CreateObject("excel.application")
Set ad = xlApp.AddIns("Analysis Toolpak")
xlApp.workbooks.Open ad.Path & "\" & "funcres.xla"
xlApp.RegisterXLL ad.Path & "\" & ad.Name
xlApp.Visible = True
With xlApp.workbooks.Add
.worksheets(1).Range("B2").Formula = "=WORKDAY(1/1/6,3,1)" '4
End With
End Sub
Sub QuitXL()
On Error Resume Next
xlApp.Quit
Set xlApp = Nothing
End Sub
Also swithching the addin's installed state from true > false > true works
(assuming it was already installed but not loaded).
Regards,
Peter T
"Peter T" <peter_t@discussions> wrote in message
news:#rOHXFrIHHA.1468@TK2MSFTNGP04.phx.gbl...
> Hi Tom,
>
> I think setting the addin's installed property to true would only load the
> addin if it was not previously installed. Perhaps something like
>
> Dim bInstalled As Boolean, ad as Object
> Set ad = xlApp.AddIns("Analysis Toolpak")
> bInstalled = ad.Installed = True
> If bInstalled Then
> ad.Installed = False
> End If
> ad.Installed = True
>
> ' restore the original installed state when done.
>
> > or you could open it like any other workbook using the open command.
>
> sPath = xlApp.Application.AddIns("Analysis Toolpak").path
> xlApp.Workbooks.Open sPath & "\" & "funcres.xla"
>
> Normally this would work with most addins but it doesn't seem to with
> Analysis Toolpak, I guess it's associated xll does not correctly load.
>
> Regards,
> Peter T
>
>
> "Tom Ogilvy" <TomOgilvy@discussions.microsoft.com> wrote in message
> news:7DDD87FB-30C9-48D4-B9D0-FEADCEAC43E2@microsoft.com...
> > I believe it would be
> >
> > Application.AddIns("Analysis Toolpak").Installed = True
> >
> > or you could open it like any other workbook using the open command.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Yumex85" wrote:
> >
> > > Thank you Tom! I think that would solve my problem.
> > > Im actually writing the code from access, coz im editting excel
> > > worksheet to link table from access after. Would you know how could i
> > > programatically open and load the Analysis toolpak?
> > >
> > > Yumi
> > >
> > >
> > > Tom Ogilvy escreveu:
> > >
> > > > when you open excel using createobject, the addins are not loaded -
> thus the
> > > > source of your probelm.
> > > >
> > > > Are you sure you need to use createobject. If you are running the
> code from
> > > > excel, (and I don't know if you are), but if so, then just open the
> workbook
> > > > in the current instance of excel. Otherwise, after you create the
new
> excel
> > > > instance you would have to add code to open the Analysis toolpak.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "yumex85@gmail.com" wrote:
> > > >
> > > > > Hello!
> > > > >
> > > > > I´m trying to programatically edit an excel worksheet with VBA,
ill
> > > > > copy part of my code below , maybe its better to understand:
> > > > >
> > > > > Set x = CreateObject("Excel.Application")
> > > > >
> > > > > If fs.FileExists(strCaminho & "Indices.xls") Then
> > > > >
> > > > > x.Workbooks.Open (strCaminho & "Indices.xls")
> > > > > x.ActiveSheet.Rows("1:1").Delete
> > > > > x.ActiveSheet.Range("2:2").Delete
> > > > > x.ActiveSheet.Range("A1:AA65536").Value =
> > > > > x.ActiveSheet.Range("A1:AA65536").Value
> > > > > Kill strCaminho & "IndicesNegoc.xls" 'deleta o arquivo
> antigo
> > > > > existente
> > > > > x.ActiveWorkbook.SaveAs fileName:=strCaminho &
> > > > > "IndicesNegoc.xls", FileFormat:=xlNormal
> > > > > x.Worksheets("MOEDAS UTILIZADAS PARA REVERSÃO").Activate
> 'muda
> > > > > de worksheet
> > > > > x.ActiveSheet.Rows("3:3").Delete 'deleção de linhas
> > > > > desnecessárias
> > > > > x.ActiveSheet.Range("4:4").Delete
> > > > > x.ActiveSheet.Range("1:1").Delete
> > > > > x.ActiveSheet.Range("1:1").Delete
> > > > > x.ActiveSheet.Range("A1:AA65536").Value =
> > > > > x.ActiveSheet.Range("A1:AA65536").Value
> > > > > Kill strCaminho & "IndicesReversao.xls"
> > > > > x.ActiveWorkbook.SaveAs fileName:=strCaminho&
> > > > > "IndicesReversao.xls",FileFormat:=xlNormal
> > > > > x.Quit
> > > > > Set x = Nothing
> > > > >
> > > > >
> > > > > As you can see, im just deleting the first lines, then i copy the
> > > > > values from Indices.xls (not the formula) and paste it, then I
save
> as
> > > > > IndicesNegoc.xls. And i do the same thing with another worksheet
> saving
> > > > > as Indices Reversao.xls. When i open IndicesNegoc.xls all the
> cells
> > > > > that used workday function appears as #NAME?
> > > > > and ive already checked if the add-in is installed. Its ok.
> > > > > What should I do to solve this problem? Thank you
> > > > >
> > > > >
> > >
> > >
>
>