Office中国论坛/Access中国论坛

标题: 恳请老鬼版主看看Excel连接表数据转移问题！ [打印本页]

作者: wang1950317 时间: 2004-12-12 06:58
标题: 恳请老鬼版主看看Excel连接表数据转移问题！
老鬼版主：您好！这是大熊版主帮忙过的帖子，在office2003版本上可以使用，在2000上不能使用。请您指点一下如何在2000上也可使用.谢谢！[attach]8098[/attach]

[em06]

作者: 老鬼 时间: 2004-12-12 08:28
我看了源代码，其原理是调用EXCEL程序打开未绑定的控件对象。你双击插入的工作表对象，看能不能调出EXCEL进行编辑，如果行，那段源码就能运行，如果不行，应该会提示安装。如果安装不了，那就没办法了。我的OFFICE无法安装那个功能，原因不明，我得再查一查。

作者: foxxp 时间: 2004-12-12 21:17
[attach]8100[/attach]

应该是高版本的数据库中的"引用库"在低版本的数据库中不能识别的问题(库中定义了VBA可以识别的资源).

从office2000所在机器上的VBA工程窗口中看一下,

工具-引用中是不是少了附件图片中蓝笔标的内容;

office2003下作的数据库,引用的内容是 : Microsoft Excel 11.0 Object Library

office2000中对应的内容好像应该是:Microsoft Excel 10.0 Object Library;

可以拉动滚动条找,找到Microsoft Excel 10.0 Object Library,在前面打勾即可,如果没有,就只好把office2000重新刷新一边,或者用office 2003了.

作者: 情比金坚 时间: 2004-12-12 21:39

以下是引用foxxp在2004-12-12 13:17:02的发言：

office2003下作的数据库,引用的内容是 : Microsoft Excel 11.0 Object Library

office2000中对应的内容好像应该是:Microsoft Excel 10.0 Object Library;

应该是：Microsoft Excel 9.0 Object Library

作者: foxxp 时间: 2004-12-12 21:51
我错了,office XP中的对应内容是10.0版

作者: wang1950317 时间: 2004-12-13 04:16
非常感谢诸位专家的指点。明天上班后再做（家中是office2003），如有问题再来请教！谢谢!谢谢！

作者: wang1950317 时间: 2004-12-13 17:45
多谢楼上斑竹、专家的指教。office重装了一下，找到了Microsoft Excel 9.0 Object Library，数据可以转移了。谢谢各位。但是还是必须按两次Update钮才行，按第一次还是出现“运行时错误，‘1004’ ‘Range‘作用于对象‘-Application‘时失败”，不知是什么原因。

作者: foxxp 时间: 2004-12-13 19:46
帮你把函数修改了一下,看行不行?

用这段脚本覆盖窗体的update按纽的click事件.由于每天的上传文件有限,就不给你发文件了.

Private Sub cmdUpdate_Click()

Dim xlsApp As Excel.Application

Dim xlsSheet As Excel.Worksheet

With Me.OLE未绑定20

.Action = acOLEActivate

.Verb = acOLEVerbOpen

End With

Set xlsApp = GetObject(, "Excel.Application")

Debug.Print xlsApp.Workbooks.Count

Set xlsSheet = xlsApp.Workbooks(xlsApp.Workbooks.Count).Worksheets(1)

xlsApp.Visible = True

On Error GoTo handle:

文本44 = xlsSheet.Range("C4").Text

文本47 = xlsSheet.Range("C5").Text

文本49 = xlsSheet.Range("C6").Text

文本51 = xlsSheet.Range("C7").Text

文本53 = xlsSheet.Range("C8").Text

文本55 = xlsSheet.Range("C9").Text

文本57 = xlsSheet.Range("C10").Text

xlsApp.Visible = False

xlsApp.Quit

Set xlsApp = Nothing

文本44.Value = 文本44

文本47.Value = 文本47

文本49.Value = 文本49

文本51.Value = 文本51

文本53.Value = 文本53

文本55.Value = 文本55

文本57.Value = 文本57

Exit Sub

handle:

Call MsgBox("错误信息" & Err.Number & ":" & Err.Description, , "错误")

Resume Next

End Sub

作者: wang1950317 时间: 2004-12-13 23:06
foxxp老师:真棒!一切都非常好!谢谢您,谢谢楼上各位,从各位那里又学到不少东西!

作者: 大熊 时间: 2004-12-14 06:12
Well done, Dear FOXAP, The main problem seems at the following sentence.You:Set xlsSheet = xlsApp.Workbooks(xlsApp.Workbooks.Count).Worksheets(1)文本44 = xlsSheet.Range("C4").TextMe文本44 = xlsApp.Range("C4").Text Can u give me the details of the difference? Mr.FoxXP?

作者: foxxp 时间: 2004-12-14 18:59
cause I found there were some sheets in OLE CALLING，see the code below

...

Debug.Print xlsApp.Workbooks.Count

...

the last one was the OLE OBJECT,others sheets were PERSONAL.XLS , XLA , XLL etc

but they were all unvisible 。

so i thought if this was the reason .

"All Were Unvisible " = "There Were No ActiveSheet" = "There Were No Current Sheet"

so we must gave the absolute reference of the sheet .

but i was puzzled why it was no error in the second try.

thanks.

作者: foxxp 时间: 2004-12-14 19:05
cause I found there were some sheets in OLE CALLING，see the code below

...

Debug.Print xlsApp.Workbooks.Count

...

the last one was the OLE OBJECT,other sheets were PERSONAL.XLS , XLA , XLL etc

but they were all unvisible 。

so i thought if this was the reason .

"All Were Unvisible " = "There Were No ActiveSheet" = "There Were No Current Sheet"

so we must gave the absolute reference of the sheet .

but i was puzzled why it was no error in the second try.

thanks.

作者: 大熊 时间: 2004-12-14 21:54
many tks. at least we know how to deal with it now.

作者: foxxp 时间: 2004-12-15 06:08
熊版见笑了.本想用英文和熊兄交流一下,没成想不仅错发了两次,语法还有多个错误.惭愧!

作者: 大熊 时间: 2004-12-16 17:02
No.I will often make mistakes too. But My teacher tell me that making Mistakes is the necessary step to study a foreign language.[em04]

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