Hi,
I have following code and output for that.
I am not sure why it is failing to open the template while fetching data for second tab.


Please suggest..


Code:
FILEDEF TEMP1 DISK TEMP1.xltm
TABLE FILE CAR
PRINT
CAR
ON TABLE HOLD AS TEMP1 FORMAT EXL07 TEMPLATE 'test.xltm' SHEETNUMBER 1
ON TABLE SET BYDISPLAY ON
END

FILEDEF TEMP2 DISK TEMP2.xlsm
TABLE FILE CAR
PRINT
COUNTRY
ON TABLE PCHOLD AS TEMP2 FORMAT EXL07 TEMPLATE 'TEMP1.xlsm' SHEETNUMBER 2
END

OUTPUT:
0 NUMBER OF RECORDS IN TABLE= 10 LINES= 10
0 XLSM FILE SAVED ...
0 NUMBER OF RECORDS IN TABLE= 5 LINES= 5
(FOC3289) TEMPLATE FILE: Error opening file
(FOC3317) Error processing template file.

Regards,
Vivek

This message has been edited. Last edited by: <Kathryn Henning>,

Vivek,

Remove your FILEDEFs, they are not required link to old post.

T

Thanks Tony for the reply.
I have removed the FILEDEFs but this did not solve the problem.
Where as I am able to resolve the issue by changing the name of hold file. It seems some hold file with name TEMP2 already exists in the session.

Please suggest, how can I clear the existing hold file before creating the new one with same name.

Regards,
Vivek

Check out the documentation for FILEDEF and the CLEAR option.

Alternatively, use a different temporary filename!

Also look at the APP DELETEF syntax to see if that would help you.

T

I'm fairly certain that the extension for the EXL07 hold file is '.xlsx' instead of '.xlsm'.

quote:

Originally posted by Wep5622:
I'm fairly certain that the extension for the EXL07 hold file is '.xlsx' instead of '.xlsm'.

Depends. If your template contains a macro, the format is .xlsm. The 'm' is for macro enabled. .xlsx files are macro free files.

jvb, you don't say what OS your Reporting Server is running. If it is Unix/Linux, then the filenames are case sensitive. We are Linux and we issue a SET FILECASE=LOWER in the edasprof, forcing all filenames to be lower case.

I suggest that after the first table request insert a -RUN and the issue the appropriate command for your Reporting Server OS and list the contents of the working directory and see what the filename is for TEMP1.

Good point, I forgot about that. Even though we have this in some of our procedures:

-SET &EXTENSION = DECODE &EXTENSION('.xltm' '.xlsm' ELSE '.xlsx');
-SET &TEMPLATE = 'UNI00025H1'|&EXTENSION;

How to create the macro file(.xltm/.xlsm) in MS Office 2013?

Steps I followed:
============
1.Create a new excel
2.Navigate to View->Macros->Record Macro and stop recording
3.Alt+F11, could see the VB macro code in Modules->Module1
4.File->Save, as a xltm format
5.Close
6.Open the excel, even after clicking the 'enable content' doesn't execute macro unless go to macro editor & press F5.

Please help me, where I'm going wrong?

Have you enabled macros in the Trust Center?

Yes...Chose, File-->Options-->Trust Center-->Macro Settings->Enable all macros(not recommended...)

Now, coming to my original problem,

Created a xltm file and place it in baseapp.I have below macro code in 'Module1'

 Sub Macro1()
'
' Macro1 Macro

    Range("A1:E10").Select
    With Selection.Font
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.499984740745262
    End With
End Sub 

 TABLE FILE CAR
  SUM RCOST
      DCOST
   BY COUNTRY
   BY CAR
ON TABLE PCHOLD FORMAT XLSX TEMPLATE 'car-test.xltm' SHEETNUMBER 1
END
-RUN 

It prompts me to open in 'xxx.xlsm' format(as documented). Only at the very first time, it prompts for "Enable Content" option and it doesn't execute macro after clicking to it. From then, never get "Enable Content" prompt and doesn't execute automatically either.

I believe with Office 2007 on up, to have an Excel Template with MAcros automatically execute any Macros, the Macros have to be in the Auto_Open Routine.
The downside to this however, also means that after they save the Template to something else and open it again at a later date the macro will re-run.

Hi Rogerwilkouk,

When you have time, kindly give me the detail steps to run macros. You can PM me as well.

Run Macro

This great document details running of Macros and at the bottom are the details of how to get them to run automatically when opening the workbook (Should work the same with Templates)

I have several base templates that I use because many of our workbooks have multiple sheets but this is the one I use the most. I got the idea for this from a post by Tony A from back in 2008.

The way to keep a macro from running again is to do the following:

Sub Auto_Open()
' This will turn of screen updating so that the changes do not flash
' on the screen at the end user. No epileptic fits thank you!
  Application.ScreenUpdating = False
  Application.PrintCommunication = False
  Sheets(1).Select
  If Sheets(1).Name = "Sheet1" Then
    Sheets(1).Select
    Sheets(1).Name = "Sheet 1"
    Call format_ws1
    Sheets(2).Select
    Sheets(2).Name = "Sheet 2"
    Call format_ws2
    ...
  End If
  Sheets(1).Select
  Application.PrintCommunication = True
' Turn screen updating on again
  Application.ScreenUpdating = True
End Sub
Sub format_ws1()
' Put formatting for Sheet 1 here
End Sub
Sub format_ws2()
' Put formatting for Sheet 2 here
End Sub

Since Sheet1 has been renamed if the user saves the workbook, the macro will not run the next time they open the workbook. With Excel 2007 and higher, tell users to save the sheet as a .xlsx and it will strip the macro from the saved workbook.

As for getting the macro to run automatically, if the Auto_Open macro name does not do the trick, you may have to change some additional Trust Center setting.

• Trying add the client server name to Trusted Locations.
  
• Under Trusted Documents, check "Allow document on a network to be trusted".
  
• If coming as an email attachment and you are using Outlook, under Protected View make sure to uncheck "Enable Protected View for Outlook attachments".
  
• Under File Block setting, makes sure "Excel 2007 and later Macro-Enabled Workbooks and Templates" is not checked.
  

This message has been edited. Last edited by: jgelona,

Many Thanks rogerwilouk and jgelona for your suggestions, I got Macro to work from Excel using Workbook_Open method. However, it doesn't help when I run it from WebFOCUS though I placed the macro in the app path, keeps throwing "run time error 1004 select method of range class failed". After a long run in Google, I figured out, shouldn't have "select" statements in my macro. Removing .select method from macro works as expected.

Private Sub Workbook_Open()
'ActiveWorkbook.Sheets("Sheet1").Select
With Worksheets("Sheet1").Range("A1:D20")
With .Font
.Name = "Arial"
.Color = vbRed
End With
End With
End Sub