在VB 2008中在工作簿/工作表之间复制和粘贴单元格 [英] Copying and pasting cells between workbooks/worksheets in VB 2008

问题描述

嗨.除了LabVIEW软件外，我在这里还是编程的新手.

在VB 2008中，我试图创建一个应用程序，该应用程序将从一个工作表("xlWS1"在第一工作簿("xlWB1")中到第二工作簿("xlWB2")中的另一个工作表("2")，从单元格A1开始.我希望通过一个按钮激活它.

然后，另一个按钮将从"xlWS1"复制相同的单元格.到每个后续按钮的"x1WB2"，工作表"3"，依此类推.
WB1/xlWS1包含每秒从我们的数据采集系统更新一次的单元格中的数据. (我们执行喷气发动机测试). "xlWB2"表格(其中6张)中将包含实时"广告中保存的数据屏幕(xlWS1，位于单元格范围内).第二个工作簿有几种不同的版本(文件名)，每个引擎模型都有一个版本.

两个工作簿都应该已经打开，但是如果它们还没有打开，我也希望应用程序打开它们.

我们一直在使用VBA宏来执行此操作，但是当有人用不同的文件名保存第二个(目标)工作簿时遇到了问题.由于某种原因，如果已经打开的Wb的文件名与该按钮的宏中的文件名不同，则宏中定义的工作簿将在宏中重命名.我试图摆脱使用宏来防止这种情况发生的问题，并使应用程序万无一失".这样更可靠.

我已经有一个可以打开适当工作簿的应用.将来我可能会合并这两个应用程序.我找不到解决方案. VBA代码在打开，激活，复制等方面非常不同和简单.基本上，我似乎找不到任何不言自明的激活/复制/粘贴等".就像我在VBA宏中使用的那样，在Vb 2008中使用了命名空间.

任何帮助将不胜感激.我敢肯定解决方案很简单，但是我似乎无法在MSDN库或我拥有的VS 2008书中找到它.书中的所有内容都涉及访问数据库，但我们尚未使用. Excel 2003工作簿/工作表没有关于此问题的任何内容.

再次感谢！

Todd Munsell
测试单元技术员
WGPW工业涡轮服务， LLC
电子邮件: [e-mail] 或 [e-mail]

Hi.  I'm new here and also fairly new to programming, except for LabVIEW software.

In VB 2008, I am trying to create an app that will copy a range of cells from one worksheet ("xlWS1") in the first workbook ("xlWB1") to another worksheet ("2") in a second workbook ("xlWB2"), starting in cell A1.  I would like this to be activated by a button.

Then, another button will copy the same cells from "xlWS1" to "xlWB2", worksheet "3", and so on for each subsequent button.

xlWB1/xlWS1 contains data in the cells that is updated from our data acquisition system once per second. (we perform jet engine testing).  "xlWB2" sheets (6 of them) will contain the saved data from the "live" screen (xlWS1, came cell range) in the first WB.  There are several different versions (filenames) of the second workbook, one for each engine model.

Both workbooks should already be open, but I would also like the app to open them if they are not open already.

We have been using VBA macros to do this, but run into problems when someone saves the second (destination) workbook under a different filename.  For some reason, if the filename of the Wb that is already open isn't the same as what's in the macro for that button, the macro's defined workbook is renamed in the macro.  I am trying to get away from using the macros to keep this from happening, and to make an app that's "foolproof" so it is more reliable.

I already have an app that will open the proper workbooks.  I may combine the two apps in the future.  I can't find a solution for this.  The VBA code is quite different and simple as far as opening, activating, copying, etc.  Basically, I can't seem to find any self-explanatory "activate/copy/paste, etc." namespace in Vb 2008 like I used in the VBA macros.

Any assistance would be greatly appreciated.  I'm sure the solution is simple, but I just can't seem to find it in the MSDN library or the VS 2008 book I have.  Everything in the book talks about accessing databases, which we don't use (yet).  There's nothing concerning this issue with Excel 2003 workbooks/worksheets.

Thanks again!

Todd Munsell
Test Cell Technician
WGPW Industrial Turbine Services, LLC
email: [e-mail] or [e-mail]

推荐答案

托德.我们是否应该假设您正在使用Visual Studio的VB完成此任务?如果不是，则此论坛顶部有一个粘性线程，其中包含非.NET Visual Basic开发的链接.

如果是.NET，则可能需要添加对您的Excel 11.0主互操作程序集(PIA)库的项目，可从此处下载:

Hi Todd.  Should we assume you are using Visual Studio's VB to accomplish this task?  if not, then there is a sticky thread at the top of this forum with links for non-.NET Visual Basic development.

If this is .NET then you may want to add a reference to your project for the Excel 11.0 Primary Interop Assembly (PIA) libraries, downloadable from here:

http://www.microsoft.com/downloads/details.aspx?familyid=3c9a983a-ac14-4125-8ba0-d36d67e0f4ad&displaylang=en

Please note that using the COM libraries can be an inherently slow process if you are "crawling" through cells in a worksheet.  But this is the most common approach.  The libraries offer virtually identical methods and properties as those you are familiar with in VBA. There are many, many examples of their use within this forum (search on Excel Interop).  *Please* be aware that, since these are COM objects (i.e. Excel App, Workbook, Worksheet, Range), they will need to be disposed of properly or else you may experience some troublesome side-effects like processes that do not go away but that keep a workbook "open" and read-only.  See the following thread for my version of properly releasing Excel COM objects:
http://social.msdn.microsoft.com/forums/en-US/vbgeneral/thread/0a18ddeb-1db4-49b7-a0cf-69e1e32311d5/


My advice is to use one of the native methods for handling large sets of data in Excel.  My personal favorite is to determine the range you need to copy (you did not indicate a problem with this step), select it and call the Copy method, which places the range data into clipboard.  Then select the upper right hand cell of the destination range and call Paste on that range (cell).  The data is stored as tab-delimited values in clipboard -- which can be tricky when you are trying to dump the copied data to anything other than Excel -- but since you are pasting into Excel again then this should come with no hitches.

这篇关于在VB 2008中在工作簿/工作表之间复制和粘贴单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！