Excel进程不关闭，code运行的每一秒时间 [英] Excel process not closing, Code runs every second time

问题描述

我有一个通过发送访问表的Excel US preadsheet并进行一些格式，并在其中输入一些公式的一些命令按钮。其他命令按钮的工作，但是这一次倒下的 LastRowInventory 行。

我相信它是与 oBook的但我不能完全弄清楚如何解决它。我认为它的，因为它试图获取它已经得到了一个对象。它运行顺利每一秒的时间，但不会关闭Excel进程。我尝试在过去的几个小时内解决这个都没有奏效。

我已经获得的错误如下：

 运行时错误'462'：远程服务器计算机不存在或不可用
 

任何帮助是AP preciated。我相信这是一个简单的解决方法，但只是不能完全得到它，我是pretty的新的节目。在code是如下。

 私人小组INVENTORYLIST_Click（）

DTable =输入框（输入表名）
************转让给EXCEL ****************** **************
昏暗strWorksheetPathTable作为字符串
    strWorksheetPathTable =○：\的GData \ Downstream_LNG \数据管理\ CEDA \报告\＆放大器; DTable和放大器; \＆安培; DTable和放大器; 的.xl​​s

DoCmd.TransferS preadsheet的transferType：= acExport，_
    US preadsheettype：= ACS preadsheetTypeExcel12，_
    表名：=（与＆amp; DTable＆安培;_INVENTORY LIST），文件名：= strWorksheetPathTable，_
    hasfieldnames：= TRUE，_
    范围：=InventoryList


************格式清单表格****************** *****************
昏暗xlApp作为对象
昏暗xlWB作为对象
设置xlApp =的CreateObject（Excel.Application）
昏暗oBook的作为Excel.Workbook
昏暗InventoryListSheet作为Excel.Worksheet
昏暗SummarySheet作为Excel.Worksheet

设置xlWB = xlApp.Workbooks.Open（与＆amp;＆strWorksheetPathTable安培;）
设置oBook的= GetObject的（与＆amp;＆strWorksheetPathTable安培;）
设置InventoryListSheet = oBook.Sheets（InventoryList）
设置SummarySheet = oBook.Sheets（概要）


随着xlWB
    随着InventoryListSheet
 一些作者preadesheet格式在这里
    结束与
结束与


************ CREATE  - 状态击穿演算对汇总表************* *********
昏暗LastRowInventory只要
LastRowInventory = oBook.Sheets（InventoryList）范围（A＆放大器; Rows.Count和放大器;）。.END（xlUp）.Row

随着xlWB
    随着SummarySheet
一些作者preadsheet公式在这里
    结束与
结束与

********订单工作表************** ***********************
随着xlWB
    .Sheets（InventoryList）。选择
    。.Sheets（InventoryList）将前：= oBook.Sheets（1）
    .Sheets（纲要）。选择
    。.Sheets（纲要）把前：= oBook.Sheets（1）
结束与

如果没有SummarySheet是没有那么
    设置SummarySheet =什么
结束如果
如果没有InventoryListSheet是没有那么
    设置InventoryListSheet =什么
结束如果
如果没有oBook的是没有那么
    设置oBook的=什么
结束如果
如果没有xlWB是没有那么
    xlWB.Save
    xlWB.Close
    设置xlWB =什么
结束如果
如果没有xlApp是没有那么
    xlApp.Quit
    设置xlApp =什么
结束如果

DoCmd.SetWarnings真

MSGBOX（清单表格已创建。）

结束小组
 

解决方案

试试这个：

  LastRowInventory = ActiveSheet.Range（B＆放大器; Rows.Count）.END（xlUp）.Row
 

或者，如果不行试试：

  LastRowInventory = ActiveSheet.Cells（Rows.Count，A）。结束（xlUp）.Row
 

这是否帮助你？

编辑：

  LastRowInventory = InventoryListSheet.Range（A＆放大器; InventoryListSheet.Rows.Count和放大器;）.END（xlUp）.Row
 

通过指定该行是在这个问题上要计算是固定的表。

I have a some command buttons that send access tables through to an excel spreadsheet and undergo some formatting and entering some formulas in them. The other command buttons work, but this one falls over at the LastRowInventory line.

I am sure its something to do with oBook but I can't quite figure out how to fix it. I think its because it is attempting to get an object it has already got. It runs smoothly every second time, but does not close the excel process. My attempts at resolving this over the last couple hours have not worked.

The error I have been getting is as follows:

Run-time error '462': The remote server machine does not exist or is unavailable

Any help is appreciated. I believe it is a simple fix but just can't quite get it, I'm pretty new to programming. The code is below.

Private Sub INVENTORYLIST_Click()

DTable = InputBox("Input Table Name")
'****************************TRANSFER TO EXCEL********************************
Dim strWorksheetPathTable As String
    strWorksheetPathTable = "O:\GData\Downstream_LNG\Data Mgmt\CEDA\Reports\" & DTable & "\" & DTable & ".xls"

DoCmd.TransferSpreadsheet transfertype:=acExport, _
    spreadsheettype:=acSpreadsheetTypeExcel12, _
    TableName:=("" & DTable & "_INVENTORY LIST"), FileName:=strWorksheetPathTable, _
    hasfieldnames:=True, _
    Range:="InventoryList"


'****************************FORMAT INVENTORY SHEET***********************************
Dim xlApp As Object
Dim xlWB As Object
Set xlApp = CreateObject("Excel.Application")
Dim oBook As Excel.Workbook
Dim InventoryListSheet As Excel.Worksheet
Dim SummarySheet As Excel.Worksheet

Set xlWB = xlApp.Workbooks.Open("" & strWorksheetPathTable & "")
Set oBook = GetObject("" & strWorksheetPathTable & "")
Set InventoryListSheet = oBook.Sheets("InventoryList")
Set SummarySheet = oBook.Sheets("Summary")


With xlWB
    With InventoryListSheet
 'Some Spreadesheet Formatting in here
    End With
End With


'****************************CREATE OE STATUS BREAKDOWN CALCULATIONS ON SUMMARY SHEET**********************
Dim LastRowInventory As Long
LastRowInventory = oBook.Sheets("InventoryList").Range("A" & Rows.Count & "").End(xlUp).Row

With xlWB
    With SummarySheet
'Some Spreadsheet Formulas here
    End With
End With

'*********************************ORDER WORKSHEETS*************************************
With xlWB
    .Sheets("InventoryList").Select
    .Sheets("InventoryList").Move Before:=oBook.Sheets(1)
    .Sheets("Summary").Select
    .Sheets("Summary").Move Before:=oBook.Sheets(1)
End With

If Not SummarySheet Is Nothing Then
    Set SummarySheet = Nothing
End If
If Not InventoryListSheet Is Nothing Then
    Set InventoryListSheet = Nothing
End If
If Not oBook Is Nothing Then
    Set oBook = Nothing
End If
If Not xlWB Is Nothing Then
    xlWB.Save
    xlWB.Close
    Set xlWB = Nothing
End If
If Not xlApp Is Nothing Then
    xlApp.Quit
    Set xlApp = Nothing
End If

DoCmd.SetWarnings True

MsgBox ("INVENTORY SHEET HAS BEEN CREATED.")

End Sub

解决方案

Try this:

LastRowInventory = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

Or if that doesn't work try:

LastRowInventory = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

Does this help you?

EDIT:

LastRowInventory = InventoryListSheet.Range("A" & InventoryListSheet.Rows.Count & "").End(xlUp).Row

By specifying the sheet that the rows are to be counted on the issue is fixed.

这篇关于Excel进程不关闭，code运行的每一秒时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！