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

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

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

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
...
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

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

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

Yumex85 escreveu:

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

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


"Yumex85" wrote in message
oups.com...
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

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