在VBA中在Access和Excel之间切换 [英] Toggling Between Access and Excel in VBA
问题描述
我很难找到如何在Access端运行的VBA代码中切换Access和Excel。
我正在阅读一个平面（文本）文件，在同一过程中同时更新Access表和Excel工作簿。
没有问题更新访问表但是出于Excel的目的，我创建了一个Access VBA用户定义函数，它有3种调用类型：OPEN，UPDATE和CLOSE，用于在第一次调用时打开工作簿，在后续调用时更新工作簿（已检索到记录）平面文件）并在最后一次调用时关闭工作簿
我已经成功完成了OPEN调用，但没有更新，因为我怀疑我必须告诉VBA我现在在做什么我进行UPDATE调用时的Excel类型处理。我当然不必经常打开和关闭工作簿来进行更新吗？
我可以看到如何使用记录集（首先在其中处理平面文件）整个）但是想了解在VBA中切换两个APPS的基本原理吗？
我在Windows 7上运行Office 2003
非常感谢提前
Phil Davis
Hi, I struggling to locate how to toggle between Access and Excel within VBA code running on the Access side.
I am reading a flat (text) file to update both Access tables and Excel workbook within the same process.
No problem updating the Access table but for Excel purposes, I have created an Access VBA User Defined Function with 3 call types: OPEN, UPDATE and CLOSE that is intended to open a workbook on the first call, update the workbook on subsequent calls (having retrieved a record fron the flat file) and close the workbook on the final call
I have succeeded with the OPEN call, but not the Update because I suspect I have to tell VBA I''m now doing Excel type processing when I make an UPDATE call. I surely don''t have to constantly open and close the workbook to make updates?
I can see how I can do this using a recordset (having first processed the flat file in its entirety) but would like to understand the basic principles of switching between the two APPS in VBA?
I am running Office 2003 on Windows 7
Many thanks in advance
Phil Davis
推荐答案
如何发布更新Excel工作簿的用户定义函数的代码？
How about posting the Code for the User Defined Function that Updates the Excel Workbook?
显然已经做出很大的努力来发布一个清晰易懂的问题。然而，正如ADezii所说，它确实可以用于发布的代码的一些相关部分。
现在用我们拥有的东西工作，我猜你有一些与Access和Excel 应用程序对象相关的一些问题。 Access 应用程序对象始终可用作应用程序。从Access VBA代码打开Excel时返回Excel 1（请参阅应用程序自动化对于某些背景）。
Excel 应用程序对象（以及一些其他Excel对象，例如工作簿） 。）应该在代码中维护，直到它们完成。对该对象中的项的任何引用都将自动视为与Excel相关的代码。重要的是将此对象（这些对象）传递给需要在Excel方面工作的任何过程。
Clearly great effort was made to post a clear and understandable question. However, as ADezii says, it really could do with some relevant bits of the code posted too.
Working for now with what we have, I''m guessing that you are having some sort of issue related to the Access and Excel Application objects. The Access Application object is always available simply as Application. The Excel one is returned when you open Excel from the Access VBA code (See Application Automation for some background).
The Excel Application object (as well as some other Excel objects like Workbook for instance.) should be maintained in the code until they are finished with. Any reference to items within that object will automatically be seen as Excel related code. It''s important that this object (these objects) are passed to any procedures that need to work on the Excel side of things.
您好NeoPa和ADezii，非常感谢您的回复。根据你在应用自动化方面的建议，我有一个小提琴，但我想我不知道如何引用在后续调用中第一次调用UDF时建立的对象。这些是对我的UDF的调用：
Hi NeoPa and ADezii, many thanks for your responses. I''ve had a fiddle following your advice in Application Automation but I think I just don''t know how to reference objects that were established in the first call to my UDF from subsequent calls. These are the call to my UDF:
这篇关于在VBA中在Access和Excel之间切换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
