Office中国论坛/Access中国论坛

标题: [求助]用VBA代码把Excel导入Access后excel程序被占用 [打印本页]

作者: xryaccess2 时间: 2006-11-6 01:48
标题: [求助]用VBA代码把Excel导入Access后excel程序被占用
我用VBA代码把Excel导入Access后excel程序被占用,并弹出是否编辑只读文件对话框,用什么代码可以关闭曾被作为数据源的excel文件到正常,并不再弹出对话框?以下是我编著写的代码:

Dim zlzl As String
Dim acc As New Excel.Application
acc.Workbooks.Open "" & Me.textexcel.Value & "", 3, False, , , , False
zlzl = "insert into " & Me.cbozl.Value & " select * from [Excel 5.0;HDR=Yes;Database=" & Me.textexcel.Value & "].[Sheet1$]"
CurrentProject.Connection.Execute (zlzl)

acc.Workbooks.Close

Set acc = Nothing
acc.Application.Quit

跪谢了!

作者: wu8313 时间: 2006-11-6 03:42
关闭正打开的文件：

========================

Attribute VB_Name = "关闭正打开的文件"
Option Compare Database

'原理：先用FindWindow找到程序窗体，然后用SendMessage向这个窗口应答。
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare Function PostMessage Lib "user32" Alias "

ostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long
Private Const WM_SYSCOMMAND = &H112
Private Const SC_MINIMIZE = &HF020&
Private Const WM_CLOSE = &H10
Private Declare Function DestroyWindow Lib "user32" (ByVal hwnd As Long) As Long
Dim windowsH As Long

Public Sub close_excelfile()

'If IsOpen(CurrentProject.Path & "\123.xls") = True Then

a = FindWindow(vbNullString, "microsoft excel - 123")
Debug.Print a
b = PostMessage(a, WM_CLOSE, 0, 0)
Debug.Print b

'End If

End Sub

作者: xryaccess2 时间: 2006-11-6 07:11
好像没什么用唉,我是在Access中编写,引用了excel9.0 ADO2.1,好像全部Excel表都不能正常打开,第一次导入还可,第二次再导入别的表就又锁定excel程序了,还请高人指点迷津,另外,还有其它把Excel表导入Access的方法吗?我用TransferSpreadsheet方法也会出现同样问题...

作者: wish2004 时间: 2006-11-6 07:51
这么复杂的啊[em06]

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)