Copying sheets
Copying sheets
Copying sheets
Copying sheets
Copying sheets
Copying sheets Copying sheets Copying sheets Copying sheets Copying sheets Copying sheets Copying sheets Copying sheets
Copying sheets Copying sheets
Copying sheets
Go Back  Xtreme Visual Basic Talk > > > Copying sheets


Reply
 
Thread Tools Display Modes
  #1  
Old 01-24-2007, 06:54 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default Copying sheets


Hi

I'm copying sheets from one workbook to another with VB.net

Dim Spreadsheet1 As New OWC10.Spreadsheet
AnotherWorksheet.UsedRange.Copy(Spreadsheet1.Worksheets("Sheet2").Cell s(1, 1))

This copies one OWC10.Worksheet to another.
No formatting is copied (it would be good if this could be though, but that's a secondary thing).

I'd like to do a paste special for VALUES only, not formula. I just want the calculations results, not all the formula in the original sheet.

Help much appreciated!

Thanks
Reply With Quote
  #2  
Old 01-24-2007, 08:43 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

I don't have much experience with the Office Web Components (OWC) controls. In the Excel office model you could either get the Range.Value property and set that value in one shot as an array, or use .PasteSpecial() to copy values only.

I don't know if the OWC has either of those capabilities. You should look using the Object Browser. If the OWC does not have these conveniences then you might need to loop through the cells, one-by-one, setting the .Vaue from one worksheet to the other for each cell...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #3  
Old 01-25-2007, 02:35 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

if You define the two ranges then You can do the following:

Code:
rnTarget.Value = rnSource.Value
Any particular reason why You use version 10.0 and not 11.0?
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #4  
Old 01-26-2007, 03:35 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default

Thanks that copies the values great
AxSpreadsheet1.Sheets("newsheet2").Range("A110").Value = AxSpreadsheet1.Sheets("newsheet1").Range("A1:A10").Value


----

Should I use 11.0 ? Its always been 10.0 we've used...any advantages of using 11.0?

I've found users have always had to download and install OWC10, even if they had Office XP installed - I wouldn't have thought that was needed if XP was installed and I included DLLs in the VB.net setup.

Does the user need to install OWC11 or something in the same way?

Thank-you very much for help - I don't know what I'd do without experts helping out!
Reply With Quote
  #5  
Old 01-26-2007, 03:41 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default

I tried lots stuff ...but no success
e.g.

AxSpreadsheet1.Sheets("newsheet2").Range("A110").Value = AxSpreadsheet1.Sheets("newsheet1").Range("A1:A10").Value
'AxSpreadsheet1.Sheets("newsheet2").Range("A1:A10").Formatting = AxSpreadsheet1.Sheets("newsheet1").Range("A1:A10").Formatting
'AxSpreadsheet1.Sheets("newsheet2").Range("A1:A10").Format = AxSpreadsheet1.Sheets("newsheet1").Range("A1:A10").Format
'AxSpreadsheet1.Sheets("newsheet2").Range("A1:A10").Font = AxSpreadsheet1.Sheets("newsheet1").Range("A1:A10").Font
'AxSpreadsheet1.Sheets("newsheet2").Range("A1:A10").Font.Bold = AxSpreadsheet1.Sheets("newsheet1").Range("A1:A10").Font.Bold
'AxSpreadsheet1.Sheets("newsheet2").Range("A1:A10").Style = AxSpreadsheet1.Sheets("newsheet1").Range("A1:A10").Style
'AxSpreadsheet1.Sheets("newsheet2").Cells("A1:A10").Style = AxSpreadsheet1.Cells("newsheet1").Cells("A1:A10").Style
'AxSpreadsheet1.Sheets("newsheet2").Cells("A1:A10").Interior.Color = AxSpreadsheet1.Cells("newsheet1").Range("A1:A10").Interior.Color

'AxSpreadsheet1.Sheets("newsheet2").Range().Value = AxSpreadsheet1.Sheets("newsheet1").UsedRange().Value
'AxSpreadsheet1.Sheets("newsheet2").Cells().Value = AxSpreadsheet1.Sheets("newsheet1").Cells().Value
Reply With Quote
  #6  
Old 01-26-2007, 04:33 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

Quote:
I've found users have always had to download and install OWC10, even if they had Office XP installed - I wouldn't have thought that was needed if XP was installed and I included DLLs in the VB.net setup.
The OWC is not automatically installed when Office XP is installed on clients machines. Have You included the whole OWC kit or just parts of it in the setup?

Quote:
Code:
AxSpreadsheet1.Sheets("newsheet2").Range().Value = AxSpreadsheet1.Sheets("newsheet1").UsedRange().Value
AxSpreadsheet1.Sheets("newsheet2").Cells().Value = AxSpreadsheet1.Sheets("newsheet1").Cells().Value
You need to use the same range width in both the source and target worksheet:

Code:
AxSpreadsheet1.Sheets("newsheet2").Range("A1:A10").Value = AxSpreadsheet1.Sheets("newsheet1").Range("A1:A10").Value
and when using cells references:

Code:
AxSpreadsheet1.Sheets("newsheet2").Cells(1,1).Value = AxSpreadsheet1.Sheets("newsheet1").Cells(1,1).Value
Do both these two approaches work for you?

Edit:
The 11.0 version has less bugs then version 10.0 However since the client's machines run Office XP (10.0) it's best to stick with version 10.0 of the spreadsheet component.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP

Last edited by DennisW; 01-26-2007 at 05:03 AM.
Reply With Quote
  #7  
Old 02-06-2007, 05:58 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default Thanks

Thanks for your help. I never got notification of new response, hence delay in responding.

I'll stick with OWC10....don't want to complicate things. I just include DLLs as part of install (the ones that VS seems to need) There seems to be a lot of them needed, even if user has OWC installed (maybe I'm going overboard here including all these?!):
astdll.dll
AxInterop.OUACTRLLib.dll
AxInterop.OWC10.dll
DLL9X.dll
DLL16.DLL
DLL32.dll
DLLNT.dll
Interop.Excel.dll
Interop.Microsoft.Office.Core.dll
Interop.MSComctlLib.DLL
Interop.Office.dll
Interop.OUACTRLLib.dll
Interop.OWC10.dll
Interop.VBIDE.dll
Microsoft.Vbe.Interop.dll
MSDATASRC.dll
MSO9.DLL
MSO.DLL
office.dll
OWC10.DLL
Reply With Quote
  #8  
Old 02-06-2007, 06:50 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

That does look like a lot, but I have no way of knowing myself one way or the other...

Basically, when you include the OWC library reference VS will add the other ActiveX DLL dependencies needed to support the OWC automatically. If this is what VS added for you, then I'm sure that this is right...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #9  
Old 02-06-2007, 09:29 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi guys,

I've not yet deployed any VB.NET solution with the OWC. However, with VB 6.0 it has been sufficient to only include the OWC10.DLL.

Hm, I find the list to be (very) long so I will actually make a setup on my own and see what the outcome actually will be with .NET, i e I'll get back later this week with the outcome.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #10  
Old 02-06-2007, 10:16 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default

thanks for your offer of help.

Its only the OWC spreadsheet I'm displaying on a form, together with a single graph page...so there does look like a lot of DLLs!
Reply With Quote
  #11  
Old 02-10-2007, 02:58 PM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi again,

I made a "quick & dirty" project with the OWC 10.0 spreadsheet component on a form. I added a setup project via the Setup Wizard. Finally I installed it on a Windows XP SP-2 configuration (.NET Framework installed but without the OWC and not Office installed). It did run as expected (but without any code that interact with the component).

The included picture shows the dependicies for the project. I believe You need to review the setup for the project.

Have You added references to Excel? If so, You should remove the reference. I'm not sure about the graph involved but I would suggest that You exclude it and start to investigate the dependicies for the Windows form with the Spreadsheet component.
Attached Images
File Type: gif OWC.gif (4.7 KB, 12 views)
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #12  
Old 02-13-2007, 12:01 PM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default thanks

thanks again for the help. I'm off this project for a while but will have a go later at the reduced DLL option!
Reply With Quote
  #13  
Old 02-22-2007, 01:58 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default

Hello DennisW

Thank-you again for your help. I also tried the quick excel form but the user gets the following message.

I added a form with text and this loads. Its when they click a button to open form that has owc component on it things don't work
(the 2nd form is only to display an OWC Excel component, it does not interaction etc)

Can you please email me your program? I would be very much obliged (I might be doing the setup wrong?).
My email is rwallacej@aol.com

Users are installing the software and cannot run it and this is causing me a lot of grief. I can't run my application without XP OWC installed (even a simple one with single form)

I woudl very much appreciate your help again. Thank-you
Attached Images
File Type: jpg untitled.JPG (22.5 KB, 6 views)
Reply With Quote
  #14  
Old 02-22-2007, 04:23 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

I wish I had some advice for you, but I'm mostly stumped...

Be aware that at a minimum the OWC controls are not cross-version aware, so if you compile to OWC 10.0 then there needs to be OWC 10.0 on that machine, not 11.0 or 9.0.


Quote:
Originally Posted by rwallacej View Post
Can you please email me your program? I would be very much obliged (I might be doing the setup wrong?).
My email is...
I'm not sure which program you are referring to. (Something Dennis mentioned?) But submissions should be done via a .zip attachment to a post on this thread. (But no compiled code; remove any .exe or .dll's from the folders first.) That way others can look at it and learn as well.


Quote:
Users are installing the software and cannot run it and this is causing me a lot of grief. I can't run my application without XP OWC installed (even a simple one with single form)
Just to be clear: this is running on your machine but not on the clients? For starters I would make sure that the correct OWC version is installed on the target computer, and I suppose the related dependencies as well, making sure that they are all the correct version (not just the correct DLL names). You might have to check the GUID's to be certain. Past that, I'm not sure...
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
  #15  
Old 02-22-2007, 04:45 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default

yes, something Dennis mentioned - he said he did a "quick & dirty" project and it run without OWC10, whcih would be great

I think the problem is clients are not installing pre-requisites....I just emailed guy who said my app wasn't working, and he hadn't installed OWC10, despite the manual clearing stating it had to be. read the manual!

if Dennis can help that's great; otherwise I'll just tell users over and over to install OWC10 :-)

thanks
Reply With Quote
  #16  
Old 02-22-2007, 07:38 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi guys,

I've sent the OWCSpreadsheet project to Your address. Please continue the discussion here in this thread enabling everyone to take part of the how it progress (or not...)
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #17  
Old 03-02-2007, 04:20 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default

This installer does work fine, thank-you - I tried it on a PC with Office but no OWC.

One thing, when I open it in Visual Studio it reports "Type Axowc10.axspreadsheet" is not defined - what do I do to get this right. Also, I'm interested to see Setup1 has only .Net as a dependency - how does it know to include the 4 OWC DLLs ?

Thank-you very much for your kindness
Reply With Quote
  #18  
Old 03-02-2007, 07:23 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

Quote:
This installer does work fine, thank-you - I tried it on a PC with Office but no OWC.
Great

When you open the project in VB.NET what does it show in the Solution Explorer for the Detected dependencies? It should look like the picture I attached in my previously post.

Except for OWC10.DLL (which actually is the whole Web Component) the other files are created by .NET as OWC is COM based and .NET need to communicate with it via interop. These are included in the Setup1.

If you run it in the debugger do you get any error message?
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #19  
Old 03-02-2007, 08:01 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default Feedback on install

Hi, the detected dependencies in only Microsoft .net framework for setup1 (even after clicking refresh)
See attached warnings
Attached Images
File Type: jpg untitled.JPG (84.1 KB, 5 views)
Reply With Quote
  #20  
Old 03-02-2007, 08:02 AM
rwallacej rwallacej is offline
Newcomer
 
Join Date: Feb 2005
Posts: 24
Default

If I add the files in your screenshot it does compile
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Copying sheets
Copying sheets
Copying sheets Copying sheets
Copying sheets
Copying sheets
Copying sheets Copying sheets Copying sheets Copying sheets Copying sheets Copying sheets Copying sheets
Copying sheets
Copying sheets
 
Copying sheets
Copying sheets
 
-->