WORKDAY #NAME? Problem - tried the toolpack already

[yumex85]

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

 

[Yumex85]

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:

 

[Peter T]

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

 

[Peter T]

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

 

[Yumex85]

Hi Peter And Tom! Thanks for the help!
I´ve tried to just set Analysis toolkit to true state and it worked
perfectly. But on the moment i copy only the values and not the formula
to the worksheet, all the fields that were using workdate r still with
the #NAME?
And I cannot leave the fields with workdate formula coz later i will
use this worksheet as a linked table on Access. And access won´t read
these fields correctly if i leave with workdate function.
Any idea why?

Regards,
Yumi

 

[Yumex85]

sorry in the previous message I meant "workday" formula and not
workdate.... so any idea ?

Yumex85 escreveu:

 

[Peter T]

Are you loading/installing the addin before or after your wb that has the
Workday function. If after try xlApp.CalculateFull.

Make Excel visible, step through and see what's going on.

I´ve tried to just set Analysis toolkit to true state

Are you saying that's all you did, if so was it already 'installed' albeit
not loaded. That alone didn't work for me without doing false > true, or the
load xla / register xll method.

Regards,
Peter T


Hi Peter And Tom! Thanks for the help!
I´ve tried to just set Analysis toolkit to true state and it worked
perfectly. But on the moment i copy only the values and not the formula
to the worksheet, all the fields that were using workdate r still with
the #NAME?
And I cannot leave the fields with workdate formula coz later i will
use this worksheet as a linked table on Access. And access won´t read
these fields correctly if i leave with workdate function.
Any idea why?

Regards,
Yumi

 

[Yumex85]

omg! It worked! Finally! Thank you very much!
You were right peter, i had to set false, then to true to install and
load it.
I was since yesterday trying to solve this problem, thanks to u guys
its all ok now!

thank you thank you

regards

Yumi