Save tables in specific excel worksheets

For questions regarding programming in the EViews programming language.

Moderators: EViews Gareth, EViews Jason, EViews Moderator, EViews Matt

RDS
Posts: 86
Joined: Thu Feb 17, 2011 9:50 am

Save tables in specific excel worksheets

Postby RDS » Tue Mar 13, 2018 3:17 pm

I would like to save tables t1, t2 and t3 in the same excel file but in different worksheets.

How can I modify the following code which save the tables in three different excel files?


for %endvar 1 2 3
t{%endvar}.save(r=A1:L23, t=csv) t{%endvar}
next

EViews Matt
EViews Developer
Posts: 560
Joined: Thu Apr 25, 2013 7:48 pm

Re: Save tables in specific excel worksheets

Postby EViews Matt » Tue Mar 13, 2018 3:30 pm

Hello,

The table object's save proc doesn't support saving to Excel files, you'll have to use the more general wfsave command instead. Take a look a this thread.

RDS
Posts: 86
Joined: Thu Feb 17, 2011 9:50 am

Re: Save tables in specific excel worksheets

Postby RDS » Wed Mar 14, 2018 1:01 am

If I am not mistaken the thread is for time series.

I do not want to export a time series, but a table created in Eviews.

Assume that I have created a table labeled "Tabletobesaved" and I would like to export this table in an excel file called macro.xls and in a sheet labeled worksheet1.

The code below gives me an error

wfsave(type=excelxml, mode=update) macro.xls @keep Tabletobesaved worksheet1


Any suggestion?

EViews Matt
EViews Developer
Posts: 560
Joined: Thu Apr 25, 2013 7:48 pm

Re: Save tables in specific excel worksheets

Postby EViews Matt » Wed Mar 14, 2018 9:23 am

My apologies, I somehow forgot mid-research that you're working with tables instead of series. Unfortunately, there's no completely automated way to do what you want. Your two basic options are to either (1) save the table in CSV format and open it in Excel, which will discard any formatting, or (2) follow the instructions in this thread for ways to preserve formatting.

EViews Steve
EViews Developer
Posts: 788
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Save tables in specific excel worksheets

Postby EViews Steve » Wed Mar 14, 2018 9:34 am

I would suggest a different method.

You can use EViews' support for OLE to do a Copy / Paste Special to save the entire contents of your table to an Excel spreadsheet. This only works if your workfile has been saved and your table has a name (not UNTITLED). Copy all the table cells you want, switch to Excel, then right-click a cell and select Paste Special. On the next dialog, select the "Paste link" option and select Text as the type.

excel_paste_special.png
excel_paste_special.png (10.18 KiB) Viewed 6577 times

This will link the cell contents to the corresponding cells in the EViews table. When the EViews table contents update, the spreadsheet can also be updated.

If you need a more programmatic way to do this, I would do it in two steps. Simply save the table as a CSV file:

Code: Select all

table01.save(t=csv) c:\files\table01.csv

Next, open the Excel spreadsheet and click the Data ribbon tab and click "From Text". That will start the text import wizard. Make sure you select "Comma" as a delimiter. Once completed, you'll have a spreadsheet that is linked to the CSV file. Now whenever you update the table in the EViews workfile, you can simply re-save it to the CSV file and then Excel will be able to get the update.

RDS
Posts: 86
Joined: Thu Feb 17, 2011 9:50 am

Re: Save tables in specific excel worksheets

Postby RDS » Wed Mar 14, 2018 10:45 am

but this does not allow me to create many worksheets.

I would like to save tables t1, t2, t3 ... t100, in the same excel file but in different worksheets automatically.

EViews Steve
EViews Developer
Posts: 788
Joined: Tue Sep 16, 2008 3:00 pm
Location: Irvine, CA

Re: Save tables in specific excel worksheets

Postby EViews Steve » Wed Mar 14, 2018 11:09 am

Yes, you can't programmatically create a single excel file with multiple sheets, each one from a different EViews table. But you can manually create one and then link each worksheet to the source CSV file to allow for easy updating.

When EViews writes a CSV file, that's simply a comma delimited text file. Microsoft Excel tends to take ownership of that file extension to say it's an Excel file type, but really it's just a simple text file. And there isn't a way to write a single CSV file that contains multiple pages of data.

Also, when EViews write an XLS or XLSX file, we re-create the entire file each time. I don't believe we ever open an existing file, add data, then save the changes. So currently there isn't a way to do what you're asking.

There are ways to write VBA script in Excel to join multiple CSV files together into a single Excel spreadsheet with multiple pages. Here's a link that talks about how to do just that.

RDS
Posts: 86
Joined: Thu Feb 17, 2011 9:50 am

Re: Save tables in specific excel worksheets

Postby RDS » Fri Mar 16, 2018 12:48 am

It would be useful if the Eviews team could create a piece of programme, which allows to export n tables in n different sheets of the same excel file, such as


for %table 1 2 3
filename.save(r=x:y, t=xls, sheet{%table}) c:\files\table
next


Return to “Programming”

Who is online

Users browsing this forum: Google [Bot] and 26 guests