❶ 请问EXCEL表格怎样可以引用外部EXCEL表格中的数据（在外部EXCEL表格不打开的情况下）
不用打开，直接引用就可以了，加上文件名。
❷ excel不打开其他表格而调用数据
通过自定义函数从不打开的工作簿中查询返回结果
自定义函数，通过给定的参数值，在另一数据表中查询，返回指定字段的值，用到了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, the listing to a VBA mole. Then, call the function with the appropriate arguments. The Sub procere 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 procere 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
❸ 有什么办法，在一个EXCEL工作表中，引用其它工作表的内容，不用同时打开也能更新这个工作表的内容。
要引用的数据来自另一个工作簿，如工作簿Book1中的SUM函数要绝对引用工作簿Book2中的数据，其公式为“=SUM([Book2]Sheet1! SA S1: SA S8,[Book2]Sheet2! SB S1: SB S9)”，也就是在原来单元格引用的前面加上“[Book2]Sheet1!”。放在中括号里面的是悉芹工作簿名称，带“!”的则是其中的工作表名称。即是跨工作簿引用枣陆档单元格或区域时，引用对象的前面必须凳乱用“!”作为工作表分隔符，再用中括号作为工作簿分隔符。
❹ excel怎样不打开文件引用其他文件数据
打开两个Excel文件，在a文件的某单元格中输稿烂入“=A15+”后，再点另一稿漏文件的某工作表中的B15，键敬漏然后回车，结果就出来了。
❺ Excel 如何用VB实现：不打开工作表、根据单元格指定文件名和工作表名，引用指定路径下相应文件中的数据
在EXCEL2016下做的测试，希望能帮助到你。是根据第一行的工作簿名称批量获取数据并在第二行自动填写对应工作表的名称（每个工作簿只含一个表）。运行代码前数据请注意备份！！
Sub test()
Dim cnn As Object
Dim strPath, strTable, strSQL, Z As String, ss$, shtName As String
Dim x%, i%
Set cnn = CreateObject("ADODB.Connection")
x = [xfd1].End(xlToLeft).Column '取得第一行最右侧单元格对应的列号，计算要获取的工作簿数量
Rows("2:25").Clear '清空数据存放区域的旧数据
For i = 1 To x
Z = Dir("D:\DATA\DATA" & "\*.xlsx")
'建立连接,提取数据
ss = Cells(1, i).Value & ".xlsx"
Do While Z <山茄> ""
If Z = ss Then
strPath = "D:\DATA\DATA" & "\" & Z
cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;Extended properties='铅档Excel 12.0;hdr=No;imex=1';data source=" & strPath
Set rst = cnn.OpenSchema(20)
shtName = rst.Fields("TABLE_NAME").Value '获取TABLE类型记录表逗激察的名称
strTable = "[" & shtName & "A1:A18]"
strSQL = "select F1 from " & strTable'获取table表第一列数据
Cells(3, i).CopyFromRecordset cnn.Execute(strSQL)
Cells(2, i) = Left(shtName, Len(shtName) - 1)'去除表名中的$符号
cnn.Close
End If
Z = Dir
Loop
Next i
Set cnn = Nothing
End Sub
❻ Excel表格引用另外一个表的数据，被引用表格不打开，引用表格上就不会显示数据，显示VALUE，急
从你的描述来看，是跨工作簿（不同的文件）引用数据，这种引用不应该用"引用另一个表"这样的描述。因为一个工作簿中可以有多个工作表，"引用另一个表"一般理解为同一工作簿中不同工作表引用数据。
用函数跨工作簿引用，多数函数是需要打开源工作簿才能显示引用结果的，这不是公式乱念携本身所能解决的问题。要使引用数据在不打开源工作簿的情况下正确显示，必须高闭用下面的方法之一：
1. VBA或其他语言工具，如c语言等；
2. SQL语句（excel带SQL）；
3. excel2013以上版本所带的Power Query，2010需要另外下哗伏载安装。
推荐方法2或3，如果有程序语言基础上可考虑方法1。但无论哪种方法，都不是三言两语能说清楚的，即使简单的教程，也得根据实际数据和具体要求做。
❼ Excel 怎样不打开Excel的情况下，提取里面的数据
不打开Excel的情况下，是不能提取里面数据的，
但是打开时可隐藏它，也就看不到了。
❽ 如何用vba不打开excel文件读写数据
不可能不打来开文件而取得数据，只源要访问文件，就得打开文件，即使是不显示出来的。不管用什么样的方式都是一样的，除非直接访问磁盘。
下面的方法可以不显示打开文件的过程，只是在内存中进行访问，速度比直接打开应该快一些。
application.workbooks.open
文件路径
application.workbooks(文件名).close
true或false表示是否保存
复制就是application.workbooks("文件名").sheets("表名").range(源单元格地址).
activesheet.range(目标单元格地址)
你可以用set
xlapp
=
createobject("excel.application")来建立一个看不到的excel应用程序对象，然后用xlapp
来替代上面的application，就会看不到打开的过程了。
❾ Excel 不打开文件时引用报错
1、告搜圆没有完全理解你说的“拖拽”是如何操作的，“数据”工作簿和“提取”工作簿同时打开的情况下，拖拽“数据”工作簿的“表2”工作表到”提取“工作簿时，“数据”工作簿中就没有“表2”这张工作表了。
2、这种跨工作簿引用数据源表（被引用的表）可以不打开，只是打开引用表时会有弹窗提示："此工作簿包含到其他数据源的链接..."，下边有“更新”、“不更新”、“帮助”三个按钮，点“更新”就可将数据源表的最新数据引用到当前表（引用数据的表）中。
3、通过公式进行跨工作簿引用时，最好将引用和被引用的工作簿放在一个目录下，迁移或分享的时候，将整个目录复制，否则就会提示编辑数据源，如果无法指定新的数据源路径，所有的引用都会出错。
4、如果数据源不是通过其他其他系统导出，而是自己编辑袜塌建立的，或者数据结构不太复杂，建议将数据源与应用分析的表格做在一个工作漏物簿中，这样便于管理，也便于书写、使用函数公式，操作起来也方便。担心数据源被意外修改的话，可以把数据源表保护起来。
5、不打开工作簿就提取数据，可以用VBA 代码实现，这样可实现的功能也更丰富。有需求的话可以在网上找视频或书籍看看，这方面资料很多。
希望以上内容能帮到你。