Copying a worksheet between EXCEL workbooks

Copying a worksheet between EXCEL workbooks

I generate data files which I want to import into Excel worksheets and display as 3-D surface plots.
I am using an EXCEL module generated using the INTEL module wizard.
Using the AUTODICE sample code as a guide. I can successfully use the WorkBooks_OpenText routine
to load data from a text file into a worksheet and I can select cells and create a 3-Dsurface chart from them on the same worksheet.

Every time I use WorkBooks_OpenText a new workbook is created. I appear to have no choice about this. so my data and their associated charts
are eventually spread over several workbooks.

What I want to do is to move/copy all the seperate worksheets in the seperate workbooks ointo sheets in a single workbook.
The following Visual Basic code does what I want (where oSheet2 is the worksheet in workbook oWB2 that I want to copy and oSheet is the sheet in a seperate work book oWB before which I want to insert oSheet2):

       ' copy worksheet between workbooks

The second line is a further test which just repeats the copy but this time inserts the copied worksheet after the third sheet in oWB.

So, basically I am asking if anyone can show me how I might be able to do this using the $Worksheet_Copy or Worksheets_Copy routines in the Excel module?

A typical call is

              SUBROUTINE $Worksheet_Copy($OBJECT, Before, After, $STATUS)

where $OBJECT is the integer handle to the worksheet I want to copy (no problem there) and Before or After are Variants describing where to copy.
Basically I have no Iidea how to construct the required Before or After variant which will do the job successfully and direct the copy to the other workbook.

I have tried using the handle to the worksheet in the other workbook after which I want the copied worksheet to be placed using

   CALL VariantInit(vInt)
     vInt%VT           = VT_I4
     vInt%VU%LONG_VAL  = worksheet

 CALL $WORKSHEET_COPY(worksheetfile,After=vInt,$status=status)
               CALL Check_Status(status, " Unable to copy worksheetfile ")

but that results in OLE error status = 0x80020009.

Any advice? Thanks in advance.

P.S. The contents of the variant can be seen in the screen shot. I note that several components of the variant are set to the same integer worksheet value even though I set only one of them.

Downloadimage/jpeg exceldebug.jpg132.85 KB
15 posts / 0 new
Last post
For more complete information about compiler optimizations, see our Optimization Notice.

Well, this is embarassing. I found the attached Help taken from my old CVF help. It seems that setting the variant type to value '9' flags the variant as an automation object. So I tried the following

   CALL VariantInit(vInt)
     vInt%VT           = 9
     vInt%VU%LONG_VAL  = worksheet

 CALL $WORKSHEET_COPY(worksheetfile,After=vInt,$status=status)
               CALL Check_Status(status, " Unable to copy worksheetfile ")

and it worked!


Downloadimage/jpeg varianthelp.jpg216.42 KB

That looks as if it's from the Visual Studio help.

Retired 12/31/2016

The help is from the Platform SDK help which is integrated into the CVF help, as the screenshot shows. I find the organisation of the CVF help extremely useful, something to be emulated, IMHO.

I bring up the CVF help using

C:\WINDOWS\HH.exe "C:\Program Files\Microsoft Visual Studio\DF98\DOC\df60.col"

By the way, I discovered that IFWINTY defines

    integer(2), parameter ::   VT_DISPATCH = 9

so for automation objects, I will use that mnemonic in future


Downloadimage/jpeg vbscripthelp.jpg332.67 KB

I liked the way VS98 did the help too. Sadly, Microsoft keeps changing the help format and functionality and we have to follow if we want it to work in VS.

Retired 12/31/2016

Anthony, can you share any more details of your code? I'm sure a lot of people with less knowledge than you would love to be able to do this. At least I wood.

The use of Excel as an almost universal data container for engineering/scientific work--whether you like it or not--has become so common, that the lack of a user-friendly (or programmer-friendly) interface to it actually detracts from using Fortran instead of migrating to some other windows-friendly language. It would be great of Intel could provide such a feature, along the lines of say the Quickwin wrapper.

I would like to help, but I only have time to tinker with my own bespoke code at the moment.
Basically, my approach has been to first examine how the AUTODICE example, that is provided with the INTEL composer, works.
Then, to use my rudimentary Visual Basic Express programming experience to code in VB what I want to do.
Where my knowledge is lacking in this area, I use the web to learn from other's queries about how to do things to EXCEL programmatically using VB.
Then I continue to try and use the EXCEL module (created using INTEL module wizard on the EXCEL 12.0 type library) to reproduce in FORTRAN what the VB code does.
When I get stuck, I ask here the sort of question I posted above.
That's it really!

I do have the feeling though that perhaps I am re-inventing, spoke by painful spoke, an exotic wheel  that has perversely been hidden somewhere.

I will post a couple of screen shots showing what I have found possible and some code extracts. If I can find the time in the future, perhaps Steve@Intel might be interested in a small knowledge-base article on my experience?

Yes, I'd be delighted to see a small article on your experience here. Excel has so many functions, writing wrappers for them would be daunting. We do provide the Module Wizard for access to the COM methods but you need to know Excel's COM methods and a bit about COM.

Retired 12/31/2016

Thanks for taking this into consideration. I understand about the time issue, and your description of your strategy is helpful in itself.

Re Intel's role: It would be great if Intel could provide, at minimum, a "sample program" that shows how to implement only the bare bones Excel operations. For myself, all I want to do is write data to a specified range of cells (including of course a single cell) in an existing xls file, and read data from a range of cells in an existing .xls file. Possibly also to create an xls file with the minimum required header structure (although I think I would do this by halving a self-make xls blank template file and using Fortran to copy that to a new working file . All of those other operations I would do in Excel, not Fortran. So, just getting the data in and out of Excel--that's the name of the game. Some cell formatting would be nice but that could come later.

If you search this forum for EXCEL, you will find that Paul Curtis has posted very useful stuff pertaining to your requirements. He is the goto person AFAIC.

dboggs, you mean like the AUTODICE sample Anthony used?

Retired 12/31/2016

I'll have to take a closer look at AUTODICE. I have been under the impression (maybe wrong) that it demonstrates a general technique, but there remains such a gap between it and general-purpose xls interfacing that there's still a lot of work to do. Or maybe just a little work for those that have a really good understanding of the way things work (which probably does not include most QUICKWIN users, by definition--we tend to be people who don't want to get bogged down in such things.

It really requires that you understand the COM interface to Excel, or at least the parts you are interested in.

Retired 12/31/2016

My feeling is that, since Visual Basic is tailor-made for handling objects and since EXCEL is supplied with Visual Basic for developers (see the 'Developer' tab in EXCEL) to write code to do spiffy things inside EXCEL (and for that matter MS ACCESS too), you need to be able to program what you want to do using Visual Basic first, so that you then know exactly what you need to do in Fortran using the EXCEL module. So get yourself a grounding in Visual Basic first (download Visual Basic Express, like I did) and all will become clearer. I admit I have some extensive experience with Visual Basic for Access which came in useful. Also, there is a great deal of help out there on the web for programmatically doing things inside EXCEL, ACCESS, you name it, with Visual Basic whereas there is almost zero help for doing it with Fortran (present company excepted).

AUTODICE shows you how to:

start an EXCEL application
Open a workbook
Open a worksheet
Select a range
copy data to a range
Chart a range of data

Which is a good start. You need to learn about Variants, methods and properties, its true, but again, there is plenty of help out there (start with a MSDN search for example) if you look for it. Wherever you see something like $Worksheet_Copy in the Excel module, you just search for 'MSDN Worksheet.Copy' (which is a worksheet method) and you can find help for it.

Your post is enlightening and inspiring. Thanks. I will investigate further.

Leave a Comment

Please sign in to add a comment. Not a member? Join today