I have A macro that changes a formula in workbooks. It goes into a list,
opens the right WB and changes the formula, printout couple pages .

the problem Is every other WB that it opens , after the formula been printed
returns #VALUE!. Tha is because it could not calculate the WORKDAY function.
knowing that, I set the macro to make sure that it opens the ATPVBAEN.XLA
file.
but the pattern persists. every other file that my macro opens print out
#VALUE!.
does any one have any guess how to solv the problem.

Sub Imprimidor()

' On Error GoTo Fim

Dim x As Long
Dim Y As Long
Dim Formul As String
Dim Seq As Long
Dim Adit As String
Dim Clie As String
Dim Cam As String
Dim Cami As String
Dim Caminho As String
Dim Essa As String
Dim Er as long

Essa = Application.ActiveWindow.Caption
Y = Range("E1")
For x = Y To 27
Seq = Cells(x, "a").Value
Adit = Cells(x, "b").Value
Clie = Cells(x, "c").Value
Cam = "\\Servidor\dados\ADITIVOS\" & Clie & "\"
Cami = "(" & Seq & ") " & Clie & " ADITIVO " & Adit & " -TC- 1008 -
N.xls"
Caminho = Cam & Cami
Debug.Print Caminho
Workbooks.Open Caminho
ActiveWorkbook.UnprotectSharing
If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess
'If ActiveWorkbook.ProtectStructure = True Then
Sheets(4).Unprotect "CECAPF"
Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)"
Analyst:
Workbooks.Open(Filename:= _
"C:\Arquivos de programas\Microsoft
Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _
).RunAutoMacros Which:=xlAutoOpen
Calculate
Workbooks(Cami).Activate
Sheets("GERAL").Range("M15").Value = Er
if Er = err.number 2015 Then GoTo Analyst
Debug.Print Application.ActiveWindow.Caption
Sheets("GERAL").Range("c2").Select
''IMPRIMINDO
Workbooks(Cami).Activate
Calculate
Sheets(4).PrintOut
MsgBox "Vire A página"
Worksheets("DETALHADA").PrintOut
Worksheets(1).Range("c2").Select
ActiveWorkbook.Close SaveChanges:=True
Workbooks(Essa).Activate
Sheets(1).Range("D" & x).Value = "ok"
Next

End Sub

Hi,

Not sure what's happening here. 1. But if you are running the program from
one machine you do not need to reinstall the Analysis ToolPak each time.
Once its installed its installed. 2. You don't need to install the Analysis
ToolPak VBA unless you are actually calculating in VBA using one of the
Analysis ToolPak functions. 3. If you are calculating in VBA using the ATP
function then you not only need to attach the ATP-VBA but you need to
reference it. That means you need to choose the command Tools, References,
and check funcres.

Maybe something in this helps.



--
Thanks,
Shane Devenshire


"Paulo" wrote:

I have A macro that changes a formula in workbooks. It goes into a list,
opens the right WB and changes the formula, printout couple pages .

the problem Is every other WB that it opens , after the formula been printed
returns #VALUE!. Tha is because it could not calculate the WORKDAY function.
knowing that, I set the macro to make sure that it opens the ATPVBAEN.XLA
file.
but the pattern persists. every other file that my macro opens print out
#VALUE!.
does any one have any guess how to solv the problem.

Sub Imprimidor()

' On Error GoTo Fim

Dim x As Long
Dim Y As Long
Dim Formul As String
Dim Seq As Long
Dim Adit As String
Dim Clie As String
Dim Cam As String
Dim Cami As String
Dim Caminho As String
Dim Essa As String
Dim Er as long

Essa = Application.ActiveWindow.Caption
Y = Range("E1")
For x = Y To 27
Seq = Cells(x, "a").Value
Adit = Cells(x, "b").Value
Clie = Cells(x, "c").Value
Cam = "\\Servidor\dados\ADITIVOS\" & Clie & "\"
Cami = "(" & Seq & ") " & Clie & " ADITIVO " & Adit & " -TC- 1008 -
N.xls"
Caminho = Cam & Cami
Debug.Print Caminho
Workbooks.Open Caminho
ActiveWorkbook.UnprotectSharing
If ActiveWorkbook.MultiUserEditing Then ActiveWorkbook.ExclusiveAccess
'If ActiveWorkbook.ProtectStructure = True Then
Sheets(4).Unprotect "CECAPF"
Sheets(4).Range("C39").Formula = "=round((0.25*$c$24),2)"
Analyst:
Workbooks.Open(Filename:= _
"C:\Arquivos de programas\Microsoft
Office\OFFICE11\Bibliote\Analise\ATPVBAEN.XLA" _
).RunAutoMacros Which:=xlAutoOpen
Calculate
Workbooks(Cami).Activate
Sheets("GERAL").Range("M15").Value = Er
if Er = err.number 2015 Then GoTo Analyst
Debug.Print Application.ActiveWindow.Caption
Sheets("GERAL").Range("c2").Select
''IMPRIMINDO
Workbooks(Cami).Activate
Calculate
Sheets(4).PrintOut
MsgBox "Vire A página"
Worksheets("DETALHADA").PrintOut
Worksheets(1).Range("c2").Select
ActiveWorkbook.Close SaveChanges:=True
Workbooks(Essa).Activate
Sheets(1).Range("D" & x).Value = "ok"
Next

End Sub