❶ 請問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 代碼實現，這樣可實現的功能也更豐富。有需求的話可以在網上找視頻或書籍看看，這方面資料很多。
希望以上內容能幫到你。