从Access打开Excel [英] Opening Excel from Access
问题描述
我有一个带有按钮的表单，它应该打开一个Excel文件（
很多宏/ VBA）。 Excel文件从Access获取它的数据
程序
这是代码
私人Sub Storage_Click（）
错误GoTo Err_Storage_Click
Dim ExcelApp As Object
Dim ExcelWasNotRunning As Boolean''最终版本的标志
Dim XLFilePath As String
Dim XLName As String''路径中的Excel文件名
Dim MyDb As Database
Dim Msg As String
''找到正常的路径
''文件夹和文件
如果是Nz （ExcelPath）=""那么
ExcelPath =" C：\ Storage.XLS"
结束如果
XLName =正确（ExcelPath，Len （ExcelPath） - InStrRev（ExcelPath，" \"））
''文件名
如果Dir（ExcelPath）< XLName那么' '找不到
XLFilePath = FindFile（&C; \Documents and Settings \Phil \ My
Documents\Access\MDB \ WFYC \ "，XLName）
Msg ="您选择的文件的名称是 &安培; vbCrLf
Msg = Msg& XLFilePath& vbCrLf
Msg = Msg& 但是原始文件是 &安培; vbCrLf
Msg = Msg& ExcelPath& vbCrLf
Msg = Msg& 你想在将来使用新名字吗？
如果MsgBox（Msg，vbQuestion + vbYesNo）= vbYes那么
ExcelPath = XLFilePath
结束如果
结束如果
设置ExcelApp = CreateObject（" Excel.Application"）
如果Err.Number< 0则ExcelWasNotRunning = True
Err.Clear''清除Err对象以防万一发生错误。
''设置用于引用要查看的文件的对象变量。
设置ExcelApp = CreateObject（" Excel.Application"）
ExcelApp.WorkBooks.Open ExcelPath， ，真''只读
ExcelApp.Visible =真
Exit_Storage_Click：
退出子
Err_Storage_Click：
如果Err = 2447那么''损坏的文件名（带＃符号）
继续下一步
Else
MsgBox Err.Description
恢复Exit_Storage_Click
结束如果
结束子
当我运行它时，我得到一个错误框ODBC Microsoft Access驱动程序登录
失败"和消息数据库已被用户置于某状态
''管理员''在机器''PHIL-DESKTOP''上阻止它被打开或锁定
我做错了什么
感谢
Phil
抱歉
应该添加
Excel应用程序中有一些代码引用数据库并且
提取相关信息
打开Excel无论数据库是否打开都没问题。
Phil
" Phil Stanton" < ph ** @ myfamilyname.co.ukwrote in message
新闻：W8 ************************** ****@posted.plusn et ...
>我有一个带有按钮的表单，该按钮应该打开一个Excel文件（含有
大量的宏/ VBA）。这个Excel文件从Access
程序获取它的数据
这是代码
Private Sub Storage_Click（）
错误GoTo Err_Storage_Click
Dim ExcelApp As Object
Dim ExcelWasNotRunning As Boolean''最终版本标志
Dim XLFilePath As String
Dim XLName As String''Excel file name from
路径
Dim MyDb As数据库
Dim Msg As String
''找到正常路径
''文件夹和文件
如果Nz（ExcelPath）=""那么
ExcelPath =" C：\ Storage.XLS"
结束如果
XLName =正确（ExcelPath，Len （ExcelPath） - InStrRev（ExcelPath，" \"））''
文件名
如果Dir（ExcelPath）< XLName那么' '找不到
XLFilePath = FindFile（&C; \Documents and Settings \Phil \ My
Documents\Access\MDB \ WFYC \ "，XLName）
Msg ="您选择的文件的名称是 &安培; vbCrLf
Msg = Msg& XLFilePath& vbCrLf
Msg = Msg& 但是原始文件是 &安培; vbCrLf
Msg = Msg& ExcelPath& vbCrLf
Msg = Msg& 你想在将来使用新名字吗？
如果MsgBox（Msg，vbQuestion + vbYesNo）= vbYes那么
ExcelPath = XLFilePath
结束如果
结束如果
设置ExcelApp = CreateObject（" Excel.Application"）
如果Err.Number< 0则ExcelWasNotRunning = True
Err.Clear''清除Err对象以防万一发生错误。
''设置用于引用要查看的文件的对象变量。
设置ExcelApp = CreateObject（" Excel.Application"）
ExcelApp.WorkBooks.Open ExcelPath， ，真''只读
ExcelApp.Visible =真
Exit_Storage_Click：
退出子
Err_Storage_Click：
如果Err = 2447那么''损坏的文件名（带＃符号）
继续下一步
Else
MsgBox Err.De划线
恢复Exit_Storage_Click
结束如果
结束子
当我跑它，我得到一个错误框ODBC Microsoft Access驱动程序登录
失败和消息数据库已被用户置于某状态
''管理员''在机器''PHIL-DESKTOP''上阻止它被打开或锁定
我做错了什么
感谢
Phil
请发布Excel文件中的代码。如果该代码引用了
数据库，那么很有可能发生错误，其中
的情况下，我们可能会更好地帮助您。 />
" Phil Stanton" < ph ** @ myfamilyname.co.ukwrote in message
news：R8 ************************** ****@posted.plusn et ...
抱歉
应该添加
Excel应用程序中有一些代码引用数据库
和
提取相关信息
打开Excel无论数据库是否打开都没问题。
Phil
" Phil Stanton" < ph ** @ myfamilyname.co.ukwrote in message
新闻：W8 ************************** ****@posted.plusn et ...
我有一个带有按钮的表单，该按钮应该打开一个Excel文件（含
很多宏/ VBA）。 Excel文件从Access获取它的数据
程序
这是代码
私人Sub Storage_Click（）
错误GoTo Err_Storage_Click
Dim ExcelApp As Object
Dim ExcelWasNotRunning As Boolean''最终版本的标志
Dim XLFilePath As String
Dim XLName As String''Excel file name from
路径
Dim MyDb作为数据库
Dim Msg As String
''找到正常路径
''文件夹和文件
如果Nz（ExcelPath）=""那么
ExcelPath =" C：\ Storage.XLS"
结束如果
XLName =正确（ExcelPath，Len （ExcelPath） - InStrRev（ExcelPath，" \"））
''
文件名
如果Dir（ExcelPath）< XLName那么''未找到
XLFilePath = FindFile （C：\Documents and Settings \Phil \ My
Documents\Access\MDB \ WFYC \，XLName）
Msg ="您选择的文件的名称是 &安培; vbCrLf
Msg = Msg& XLFilePath& vbCrLf
Msg = Msg& 但是原始文件是 &安培; vbCrLf
Msg = Msg& ExcelPath& vbCrLf
Msg = Msg& 你想在将来使用新名字吗？
如果MsgBox（Msg，vbQuestion + vbYesNo）= vbYes那么
ExcelPath = XLFilePath
结束如果
结束如果
设置ExcelApp = CreateObject（" Excel.Application"）
如果Err.Number< 0则ExcelWasNotRunning = True
Err.Clear''清除Err对象以防万一发生错误。
''设置用于引用要查看的文件的对象变量。
设置ExcelApp = CreateObject（" Excel.Application"）
ExcelApp.WorkBooks.Open ExcelPath， ，True''读
只有
ExcelApp.Visible = True
Exit_Storage_Click：
退出Sub
Err_Storage_Click：
如果Err = 2447 n''损坏的文件名（带＃符号）
继续下一步
否则
MsgBox Err.Description
恢复Exit_Storage_Click
结束如果
结束子
当我运行它时，我得到一个错误框 ODBC Microsoft Access驱动程序登录
失败和消息数据库已被用户置于某状态
''管理员''在机器''PHIL-DESKTOP'上阻止它被打开或
已锁定
我做错了什么
感谢
Phil
Hi Phil，
我想问一些事情，以便我能正确理解这个
：您是否正在使用Access应用程序和Excel
应用程序打开时间？也就是说，你有Excel和/或
和Access同时运行，然后从Excel尝试用Access对象打开
相同的数据库，反之亦然？
干杯
青蛙
I have a form with a button which is supposed to open an Excel file (With
lots of Macros /VBA) in it. The Excel file gets it''s data from the Access
program
Here is the code
Private Sub Storage_Click()
On Error GoTo Err_Storage_Click
Dim ExcelApp As Object
Dim ExcelWasNotRunning As Boolean '' Flag for final release
Dim XLFilePath As String
Dim XLName As String '' Excel file name from Paths
Dim MyDb As Database
Dim Msg As String
'' Find the normal path
'' Folder and File
If Nz(ExcelPath) = "" Then
ExcelPath = "C:\Storage.XLS"
End If
XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\"))
'' File Name
If Dir(ExcelPath) <XLName Then '' Not found
XLFilePath = FindFile("C:\Documents and Settings\Phil\My
Documents\Access\MDB\WFYC\", XLName)
Msg = "The name of the file you have selected is " & vbCrLf
Msg = Msg & XLFilePath & vbCrLf
Msg = Msg & "but the original file was " & vbCrLf
Msg = Msg & ExcelPath & vbCrLf
Msg = Msg & "Do you want to use the new name in future?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
ExcelPath = XLFilePath
End If
End If
Set ExcelApp = CreateObject("Excel.Application")
If Err.Number <0 Then ExcelWasNotRunning = True
Err.Clear '' Clear Err object in case error occurred.
'' Set the object variable to reference the file you want to see.
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.WorkBooks.Open ExcelPath, , True '' Read Only
ExcelApp.Visible = True
Exit_Storage_Click:
Exit Sub
Err_Storage_Click:
If Err = 2447 Then '' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Click
End If
End Sub
When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
Failed" and the message "The database has been placed in a state by user
''Admin'' on Machine ''PHIL-DESKTOP'' that prevents it being opened or locked"
What am I doing wrong
Thank
Phil
Sorry
Should have added
There is some code in the Excel application which refers to the Database and
extracts the relevant information
No problem opening Excel whether the database is open or not.
Phil
"Phil Stanton" <ph**@myfamilyname.co.ukwrote in message
news:W8******************************@posted.plusn et...>I have a form with a button which is supposed to open an Excel file (With
lots of Macros /VBA) in it. The Excel file gets it''s data from the Access
program
Here is the code
Private Sub Storage_Click()
On Error GoTo Err_Storage_Click
Dim ExcelApp As Object
Dim ExcelWasNotRunning As Boolean '' Flag for final release
Dim XLFilePath As String
Dim XLName As String '' Excel file name from
Paths
Dim MyDb As Database
Dim Msg As String
'' Find the normal path
'' Folder and File
If Nz(ExcelPath) = "" Then
ExcelPath = "C:\Storage.XLS"
End If
XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\")) ''
File Name
If Dir(ExcelPath) <XLName Then '' Not found
XLFilePath = FindFile("C:\Documents and Settings\Phil\My
Documents\Access\MDB\WFYC\", XLName)
Msg = "The name of the file you have selected is " & vbCrLf
Msg = Msg & XLFilePath & vbCrLf
Msg = Msg & "but the original file was " & vbCrLf
Msg = Msg & ExcelPath & vbCrLf
Msg = Msg & "Do you want to use the new name in future?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
ExcelPath = XLFilePath
End If
End If
Set ExcelApp = CreateObject("Excel.Application")
If Err.Number <0 Then ExcelWasNotRunning = True
Err.Clear '' Clear Err object in case error occurred.
'' Set the object variable to reference the file you want to see.
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.WorkBooks.Open ExcelPath, , True '' Read Only
ExcelApp.Visible = True
Exit_Storage_Click:
Exit Sub
Err_Storage_Click:
If Err = 2447 Then '' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Click
End If
End Sub
When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
Failed" and the message "The database has been placed in a state by user
''Admin'' on Machine ''PHIL-DESKTOP'' that prevents it being opened or locked"
What am I doing wrong
Thank
Phil
Please post the code that is in the Excel file. If that code refers to the
database, then that is most likely where the error is occuring, in which
case, we might be better able to help you.
"Phil Stanton" <ph**@myfamilyname.co.ukwrote in message
news:R8******************************@posted.plusn et...Sorry
Should have added
There is some code in the Excel application which refers to the Databaseand
extracts the relevant information
No problem opening Excel whether the database is open or not.
Phil
"Phil Stanton" <ph**@myfamilyname.co.ukwrote in message
news:W8******************************@posted.plusn et...I have a form with a button which is supposed to open an Excel file (With
lots of Macros /VBA) in it. The Excel file gets it''s data from the Access
program
Here is the code
Private Sub Storage_Click()
On Error GoTo Err_Storage_Click
Dim ExcelApp As Object
Dim ExcelWasNotRunning As Boolean '' Flag for final release
Dim XLFilePath As String
Dim XLName As String '' Excel file name from
Paths
Dim MyDb As Database
Dim Msg As String
'' Find the normal path
'' Folder and File
If Nz(ExcelPath) = "" Then
ExcelPath = "C:\Storage.XLS"
End If
XLName = Right(ExcelPath, Len(ExcelPath) - InStrRev(ExcelPath, "\"))
''
File Name
If Dir(ExcelPath) <XLName Then '' Not found
XLFilePath = FindFile("C:\Documents and Settings\Phil\My
Documents\Access\MDB\WFYC\", XLName)
Msg = "The name of the file you have selected is " & vbCrLf
Msg = Msg & XLFilePath & vbCrLf
Msg = Msg & "but the original file was " & vbCrLf
Msg = Msg & ExcelPath & vbCrLf
Msg = Msg & "Do you want to use the new name in future?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbYes Then
ExcelPath = XLFilePath
End If
End If
Set ExcelApp = CreateObject("Excel.Application")
If Err.Number <0 Then ExcelWasNotRunning = True
Err.Clear '' Clear Err object in case error occurred.
'' Set the object variable to reference the file you want to see.
Set ExcelApp = CreateObject("Excel.Application")
ExcelApp.WorkBooks.Open ExcelPath, , True '' Read
Only
ExcelApp.Visible = True
Exit_Storage_Click:
Exit Sub
Err_Storage_Click:
If Err = 2447 Then '' Corrupted File name (with # sign)
Resume Next
Else
MsgBox Err.Description
Resume Exit_Storage_Click
End If
End Sub
When I run it, I get an Error Box "ODBC Microsoft Access Driver Login
Failed" and the message "The database has been placed in a state by user
''Admin'' on Machine ''PHIL-DESKTOP'' that prevents it being opened or
locked"
What am I doing wrong
Thank
Phil
Hi Phil,
I would just like to ask something so that I can understand this
correctly: Are you having the Access application and the Excel
application open at the same time? That is to say, have you got Excel
and Access running at the same time, then from Excel try to open the
same database with an Access object, and vice versa?
Cheers
The Frog
这篇关于从Access打开Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
