WORKDAY #NAME? Problem - tried the toolpack already
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" wrote in message
...
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
" 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
|