excel不打开其他表格而调用数据

Excel表格引用另外一个表的数据，被引用表格不打开，引用表格上就不会显示数据，显示VALUE，急~

从你的描述来看，是跨工作簿（不同的文件）引用数据，这种引用不应该用"引用另一个表"这样的描述。因为一个工作簿中可以有多个工作表，"引用另一个表"一般理解为同一工作簿中不同工作表引用数据。
用函数跨工作簿引用，多数函数是需要打开源工作簿才能显示引用结果的，这不是公式本身所能解决的问题。要使引用数据在不打开源工作簿的情况下正确显示，必须用下面的方法之一：
1. VBA或其他语言工具，如c语言等；
2. SQL语句（excel带SQL）；
3. excel2013以上版本所带的Power Query，2010需要另外下载安装。
推荐方法2或3，如果有程序语言基础上可考虑方法1。但无论哪种方法，都不是三言两语能说清楚的，即使简单的教程，也得根据实际数据和具体要求做。

打开表1，点击你需要插入表2数据的单元格，按=，然后点击表2，点击你需要的数据，回车即可。

通过自定义函数从不打开的工作簿中查询返回结果
自定义函数，通过给定的参数值，在另一数据表中查询，返回指定字段的值，用到了ADO，数组，
希望对大家有用，我自己用在查询上市公司的常用指标，输入代码，日期，就能返回值
Dim cha As String '查询的字段
Dim Arr1 As Variant

'Public Function qu(daima As String, riqi)
'Dim conn As ADODB.Connection
'Dim rst1 As ADODB.Recordset
'Dim strsql As String
''Dim Arr1 As Variant
'Dim x
''Dim daima As String
' daima = "'" & daima & ".sz'"
' riqi = "#" & riqi & "#"
' Set conn = New ADODB.Connection '创建一个连接和打开 Cnn 连接
' Set rst1 = New ADODB.Recordset '创建记录集
'
' Set conn = CreateObject("adodb.connection")
'conn.Open "Provider=Microsoft.Jet.Oledb.4.0;" & _
'"Extended Properties=Excel 8.0;" & _
'"Data Source=" & "e:" & "\" & "常用指标.xls"
'
'strsql = "SELECT * FROM [数据表$] WHERE 取数期间=" & riqi & " and 代码=" & daima
' rst1.Open strsql, conn, 1, 3 '取得记录集
'Arr1 = rst1.GetRows()
'
'qu = Arr1(4, 0)
'
'rst1.Close '清空记录集
' conn.Close
'Set conn = Nothing
'
'End Function

Public Sub ss(daima As String, riqi, cha)
Dim conn As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim strsql As String
'Dim Arr1 As Variant
Dim x
'Dim daima As String
daima = "'" & daima & ".sz'"
riqi = "#" & riqi & "#"
Set conn = New ADODB.Connection '创建一个连接和打开 Cnn 连接
Set rst1 = New ADODB.Recordset '创建记录集

Set conn = CreateObject("adodb.connection")
conn.Open "Provider=Microsoft.Jet.Oledb.4.0;" & _
"Extended Properties=Excel 8.0;" & _
"Data Source=" & "e:" & "\" & "常用指标.xls"

strsql = "SELECT " & cha & " FROM [数据表$] WHERE 取数期间=" & riqi & " and 代码=" & daima
rst1.Open strsql, conn, 1, 3 '取得记录集
Arr1 = rst1.GetRows()

'qu = Arr1(4, 0)

rst1.Close '清空记录集
conn.Close
Set conn = Nothing

End Sub

Public Function qu_min(daima As String, riqi)
cha = "名称"
Call ss(daima, riqi, cha)
qu_min = Arr1(0, 0)

End Function
Public Function qu_sssj(daima As String, riqi)
cha = "上市时间"
Call ss(daima, riqi, cha)
qu_sssj = Arr1(0, 0)

End Function
Public Function qu_guben(daima As String, riqi)
cha = "股本"
Call ss(daima, riqi, cha)
qu_guben = Arr1(0, 0)

End Function
Public Function qu_shouru(daima As String, riqi)
cha = "主营业务收入"
Call ss(daima, riqi, cha)
qu_shouru = Arr1(0, 0)
End Function
Public Function qu_jinlirun(daima As String, riqi)
cha = "净利润"
Call ss(daima, riqi, cha)
qu_jinlirun = Arr1(0, 0)
End Function
Public Function qu_maolilv(daima As String, riqi)
cha = "毛利率"
Call ss(daima, riqi, cha)
qu_maolilv = Arr1(0, 0)
End Function
Public Function qu_jinlilv(daima As String, riqi)
cha = "净利率"
Call ss(daima, riqi, cha)
qu_jinlilv = Arr1(0, 0)
End Function
Public Function qu_jzcshouyilv(daima As String, riqi)
cha = "净资产收益率"
Call ss(daima, riqi, cha)
qu_jzcshouyilv = Arr1(0, 0)
End Function
Public Function qu_zcfzlv(daima As String, riqi)
cha = "资产负债率"
Call ss(daima, riqi, cha)
qu_zcfzlv = Arr1(0, 0)
End Function
Public Function qu_gujia(daima As String, riqi)
cha = "股价"
Call ss(daima, riqi, cha)
qu_gujia = Arr1(0, 0)
End Function
Public Function qu_zhaogusyl(daima As String, riqi)
cha = "招股市盈率"
Call ss(daima, riqi, cha)
qu_zhaogusyl = Arr1(0, 0)
End Function
Public Function qu_mgsy(daima As String, riqi)
cha = "每股收益"
Call ss(daima, riqi, cha)
qu_mgsy = Arr1(0, 0)
End Function
Public Function qu_syl(daima As String, riqi)
cha = "市盈率"
Call ss(daima, riqi, cha)
qu_syl = Arr1(0, 0)
End Function
Public Function qu_sjl(daima As String, riqi)
cha = "市净率"
Call ss(daima, riqi, cha)
qu_sjl = Arr1(0, 0)
End Function
Public Function qu_mgxjl(daima As String, riqi)
cha = "每股现金流"
Call ss(daima, riqi, cha)
qu_mgxjl = Arr1(0, 0)
End Function
Public Function qu_mgjyxjl(daima As String, riqi)
cha = "每股经营活动现金流"
Call ss(daima, riqi, cha)
qu_mgjyxjl = Arr1(0, 0)
End Function
Public Function qu_ldbl(daima As String, riqi)
cha = "流动比率"
Call ss(daima, riqi, cha)
qu_ldbl = Arr1(0, 0)
End Function
Public Function qu_sdbl(daima As String, riqi)
cha = "速动比率"
Call ss(daima, riqi, cha)
qu_sdbl = Arr1(0, 0)
End Function
Public Function qu_zzczzl(daima As String, riqi)
cha = "总资产周转率"
Call ss(daima, riqi, cha)
qu_zzczzl = Arr1(0, 0)
End Function
Public Function qu_chzzl(daima As String, riqi)
cha = "存货周转率"
Call ss(daima, riqi, cha)
qu_chzzl = Arr1(0, 0)
End Function
Public Function qu_yszkzzl(daima As String, riqi)
cha = "应收账款周转率"
Call ss(daima, riqi, cha)
qu_yszkzzl = Arr1(0, 0)
End Function
Public Function qu_yhlxbs(daima As String, riqi)
cha = "已获利息倍数"
Call ss(daima, riqi, cha)
qu_yhlxbs = Arr1(0, 0)
End Function
Public Function qu_yysrzzl(daima As String, riqi)
cha = "营业总收入同比增长率"
Call ss(daima, riqi, cha)
qu_yysrzzl = Arr1(0, 0)
End Function
Public Function qu_jlrzzl(daima As String, riqi)
cha = "净利润同比增长率"
Call ss(daima, riqi, cha)
qu_jlrzzl = Arr1(0, 0)
End Function
Public Function qu_jzcsylzzl(daima As String, riqi)
cha = "净资产收益率同比增长率"
Call ss(daima, riqi, cha)
qu_jzcsylzzl = Arr1(0, 0)
End Function
Public Function qu_zjlfl(daima As String, riqi)
cha = "证监会行业分类"
Call ss(daima, riqi, cha)
qu_zjlfl = Arr1(0, 0)
End Function
Public Function qu_windfl(daima As String, riqi)
cha = "wind行业分类"
Call ss(daima, riqi, cha)
qu_windfl = Arr1(0, 0)
End Function
Public Function qu_clrq(daima As String, riqi)
cha = "成立日期"
Call ss(daima, riqi, cha)
qu_clrq = Arr1(0, 0)
End Function
Public Function qu_fddb(daima As String, riqi)
cha = "法定代表人"
Call ss(daima, riqi, cha)
qu_fddb = Arr1(0, 0)
End Function
Public Function qu_dydgd(daima As String, riqi)
cha = "第一大股东"
Call ss(daima, riqi, cha)
qu_dydgd = Arr1(0, 0)
End Function

Public Function qu_cgbl(daima As String, riqi)
cha = "第一大股东持股比例"
Call ss(daima, riqi, cha)
qu_cgbl = Arr1(0, 0)
End Function
不打开文件提取数据
A VBA Function to Get a Value From a Closed File
VBA does not include a method to retrieve a value from a closed file. You can, however, take advantage of Excel's ability to work with linked files.

This tip contains a VBA function that retrieves a value from a closed workbook. It does by calling an XLM macro.

Note:
You cannot use this function in a worksheet formula.

The GetValue Function
The GetValue function, listed below takes four arguments:

path: The drive and path to the closed file (e.g., "d:\files")
file: The workbook name (e.g., "99budget.xls")
sheet: The worksheet name (e.g., "Sheet1")
ref: The cell reference (e.g., "C4")
Private Function GetValue(path, file, sheet, ref)
' Retrieves a value from a closed workbook
Dim arg As String

' Make sure the file exists
If Right(path, 1) <> "\" Then path = path & "\"
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If

' Create the argument
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)

' Execute an XLM macro
GetValue = ExecuteExcel4Macro(arg)
End Function

Using the GetValue Function
To use this function, copy the listing to a VBA module. Then, call the function with the appropriate arguments. The Sub procedure below demonstrates. It simply displays the value in cell A1 in Sheet1 of a file named 99Budget.xls, located in the XLFiles\Budget directory on drive C:.

Sub TestGetValue()
p = "c:\XLFiles\Budget"
f = "99Budget.xls"
s = "Sheet1"
a = "A1"
MsgBox GetValue(p, f, s, a)
End Sub
Another example is shown below. This procedure reads 1,200 values (100 rows and 12 columns) from a closed file, and places the values into the active worksheet.

Sub TestGetValue2()
p = "c:\XLFiles\Budget"
f = "99Budget.xls"
s = "Sheet1"
Application.ScreenUpdating = False
For r = 1 To 100
For c = 1 To 12
a = Cells(r, c).Address
Cells(r, c) = GetValue(p, f, s, a)
Next c
Next r
Application.ScreenUpdating = True
End Sub
Caveat
In order for this function to work properly, a worksheet must be active in Excel. It will generate an error if all windows are hidden, or if the active sheet is a Chart sheet

这好比想从一个密封的瓶子里拿东西出来一样.不打开瓶子?纯属无稽之谈.
要从任何一个文件中调用数据,这个文件一定要被打开,只是看你用何种方式打开而已.

可以不打开调用的，与所在的盘符没有关系。
只要保证被调用的文件在调用文件所在的目录下没有同名的，以及被调用文件在所在的目录下存在。

你编辑的时候打开，关闭后也可以调用的。

='盘符\目录\[文件名.xls]表名'!$A$1

---

额敏县15643654396： excel怎样不打开文件引用其他文件数据 - ？
湛卸欣然： 打开两个Excel文件,在a文件的某单元格中输入“=A15+”后,再点另一文件的某工作表中的B15,然后回车,结果就出来了.

额敏县15643654396： 在 Excel 中如何不打开基础表,就可引用其中的数据 - ？
湛卸欣然： 1.公式法 Sub GetDate() Dim WbPath As String WbPath = ＂'＂ & ThisWorkbook.Path & ＂\[数据.xlsx]Sheet1'!＂ With Range(＂a1:h24＂) .Clear .FormulaR1C1 = ＂=＂ & WbPath & ＂rc＂ .Value = .Value End WithEnd S2.GetObject法 Sub ...

额敏县15643654396： 请问EXCEL表格怎样可以引用外部EXCEL表格中的数据?(在外部EXCEL表格不打开的情况下)？
湛卸欣然： 不用打开,直接引用就可以了,加上文件名.

额敏县15643654396： excel 2007 xlsm不打开其他工作薄,引用数据,有些可以 有些不能引用,原因?? - ？
湛卸欣然： 首先,请确认被引用工作簿数据是不是为空 其次,不打开被引用工作簿会造成数据得不到更新

额敏县15643654396： 在excel里跨工作簿做有效性,怎样才能不打开引用源就能做引用 - ？
湛卸欣然： 跨工作簿没听过,跨工作表可以实现.引用其他工作簿时,可以没有打开.但打开操作比较方便.

额敏县15643654396： 在Excel中,怎样直接引用另一个表格的数据 - ？
湛卸欣然： Excel中利用等于某个单元格,就可以直接引用另外一个表格的数据.软件版本:Office2013 方法如下:1.Sheet1中有如下数据:2.在Sheet2中输入如下公式:3.就实现了在Sheet2中直接引用Sheet1中的数据:

额敏县15643654396： excel 2007已打开的工作表如何调用未打开的工作表的数据 - ？
湛卸欣然： 楼主好,输入='工作表路径[工作表名]sheet名称'!单元格名称,如='C:\Users\think\Desktop\[新建 Microsoft Office Excel 工作表.xlsx]Sheet1'!A1,则可以调用关闭的A1数据.也可进行计算:)

额敏县15643654396： EXCEL表格如何通过函数引用数据 - ？
湛卸欣然： 1首先最好打开那个文件,然后在那excel文件中按一个＂=＂号然后把鼠标移到另一个excel文件(就是你刚打开的那个)中的数据或单元格,那里点一下就可以了. 2注意当的你按下＂=＂后鼠标不要按到别的地方.

额敏县15643654396： excel 代码调用另外excel表格 - ？
湛卸欣然： 如果是宏,可以先打开另一个文件,再用常规方法获取单元格内容.举例:Workbooks.Open (＂C:\Documents and Settings\Administrator\桌面\新建文件夹\工作表2.xls＂) Set wb = ActiveWorkbook x = wb.Worksheets(1).Range(＂E14＂) '读取单元格内容 wb.Close

额敏县15643654396： excel怎么调用另外一个表格的数据,而且还是在新表只把原表的奇偶行做成两列? - ？
湛卸欣然： 问题1:在表1的A1输入公式:=INDEX(表2!$A:$A,ROW()*2-(COLUMN()=1)) 将公式向右向下复制.问题2:将新数据粘贴到表2的A列即可.