WORKDAY #NAME? Problem - tried the toolpack already
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 |
WORKDAY #NAME? Problem - tried the toolpack already
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 |
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 |
WORKDAY #NAME? Problem - tried the toolpack already
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 |
WORKDAY #NAME? Problem - tried the toolpack already
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 |
WORKDAY #NAME? Problem - tried the toolpack already
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 |
WORKDAY #NAME? Problem - tried the toolpack already
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 |
WORKDAY #NAME? Problem - tried the toolpack already
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 |
All times are GMT +1. The time now is 03:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com