Board logo

標題: EXCEL抓取CSV資料並即時更新 [打印本頁]
作者: jasonwu0114    時間: 2013-12-16 14:50     標題: 不開啟檔案下讀取資料的路徑/檔名設定為變數問題

欲讀取的EXCEL檔名中都有當天日期(ex:ooxxx1021216.xls)
想做一張彙總表存放在另一個資料夾
彙總表A欄位是我要的日期
[attach]17062[/attach]
以不開啟檔案方式來讀取資料
有試過直接用     "短期股權投資部位及評價控管表1021216.xls"  是抓到資料
但改成" 短期股權投資部位及評價控管表" &  dd  & ".xls"
用變數方式就不行
出現要我選取檔案的畫面
  [attach]17061[/attach]
應該是變數設定錯誤
請問
1.變數要如何設定??
2.還有其他更方便的方式來讀取未開啟檔案資料??


Sub checkclosedfile1()
Dim strpath As String, strfile As String
Dim strsheet As String, strresult As String
    strpath = "X:\風管報表\短期股權投資部位及評價控管表"
    strfile = "短期股權投資部位及評價控管表1021202.xls"
    strsheet = "全公司I"
    strresult = getcellvalue(strpath, strfile, strsheet, "i1")
Cells(2, 2) = strresult
End Sub

Public Function getcellvalue(strpath As String, strfile As String, strsheet As String, stra1 As String)
    getcellvalue = ExecuteExcel4Macro("'" & "[" & strfile & "]" & strsheet & "'!" & Range(stra1).Address(, , xlR1C1))
End Function
作者: stillfish00    時間: 2013-12-16 16:58

回復 1# jasonwu0114
附上不行的程式碼看看
作者: jasonwu0114    時間: 2013-12-17 16:28

本帖最後由 jasonwu0114 於 2013-12-17 16:29 編輯

找到比較簡單寫法
想請教
假如sheet1A欄有一些日期
是我要抓的檔案名稱一部分(紅字部分)
[IRS交易部位評價表1021216.xls]
如果要把資料抓回放在B欄要如何寫如何設定變數??

另外
一次只能抓一個欄位嗎??

Sub nn()
[A1] = ExecuteExcel4Macro("'X:\風管報表\利率交換部位評價表\[IRS交易部位評價表1021216.xls]部位評價表'!R1C13")
End Sub
作者: jasonwu0114    時間: 2013-12-18 11:33

本帖最後由 jasonwu0114 於 2013-12-18 11:35 編輯

不好意思已解決如下
請問還可以寫的更SMART嗎??
另外yy = "'X:\風管報表\交換部位評價表\[IRS部位評價表" & kk & ".xls]部位評價表'!R1C13"
R1C13這裡只能是單一欄位嗎??

Sub nn4()
Dim yy As String
Dim kk As Range
Set kk = Sheets("工作表1").Range("A3")
Do While kk <> ""
    yy = "'X:\風管報表\交換部位評價表\[IRS部位評價表" & kk & ".xls]部位評價表'!R1C13"
    kk.Offset(0, 3) = ExecuteExcel4Macro(yy)
    Set kk = kk.Offset(1)
Loop
End Sub
作者: stillfish00    時間: 2013-12-18 12:41

本帖最後由 stillfish00 於 2013-12-18 12:45 編輯

回復 4# jasonwu0114
  1. Sub nn4()
  2.   With Sheets("工作表1")
  3.     With .Range(.[D3], .Cells(.Rows.Count, "D").End(xlUp))
  4.       .FormulaR1C1 = "='X:\風管報表\交換部位評價表\[IRS部位評價表" & .Parent.[A3].Value & ".xls]部位評價表'!R1C13"
  5.       .Value = .Value
  6.     End With
  7.   End With
  8. End Sub
複製代碼
作者: jasonwu0114    時間: 2013-12-18 16:19

回復 5# stillfish00

厲害厲害
又學到好幾招
可是還有一點問題
D3~8欄如果原本是空格執行後D1~D3會填以A3為參數找到的資料
               如果有數字執行後D3~D8會填以A3為參數找到的資料
要改哪??
還有    With .Range(.[D3], .Cells(.Rows.Count, "D").End(xlUp))  的意思??

    [attach]17078[/attach]
作者: stillfish00    時間: 2013-12-18 18:23

本帖最後由 stillfish00 於 2013-12-18 18:30 編輯

回復 6# jasonwu0114
  1. Sub nn4()
  2.   With Sheets("工作表1")
  3.     With .Range(.[A3], .Cells(.Rows.Count, "A").End(xlUp)).offset(,3)
  4.       .FormulaR1C1 = "='X:\風管報表\交換部位評價表\[IRS部位評價表" & .Parent.[A3].Value & ".xls]部位評價表'!R1C13"
  5.       .Value = .Value
  6.     End With
  7.   End With
  8. End Sub
複製代碼
.Rows.Count  :  Excel最大行數
.Cells(.Rows.Count, "A")  :  A欄最下面一格
.Cells(.Rows.Count, "A").End(xlUp)  :  從A欄最下面一格往上找到有資料的儲存格
.Range(.[A3], .Cells(.Rows.Count, "A").End(xlUp))  :  從A3到A欄最後一筆資料形成的範圍
.Range(.[A3], .Cells(.Rows.Count, "A").End(xlUp)).offset(,3)  :  從A3到A欄最後一筆資料形成的範圍，這範圍再往右移動三欄
作者: jasonwu0114    時間: 2013-12-19 09:48

回復 7# stillfish00
不好意思還要再麻煩
執行後還是以A3為參數去抓資料
  1. .FormulaR1C1 = "='X:\風管報表\交換部位評價表\[IRS部位評價表" & .Parent.[A3].Value & ".xls]部位評價表'!R1C13"
複製代碼
應該是要改這.Parent.[A3]嗎
如何改??

感恩!!
作者: stillfish00    時間: 2013-12-19 10:13

回復 8# jasonwu0114
我好像誤會你的意思了，是這樣嗎?
  1. Sub nn5()
  2.   Dim x
  3.   With Sheets("工作表1")
  4.     For Each x In .Range(.[A3], .Cells(.Rows.Count, "A").End(xlUp)).Offset(, 3)
  5.       x.Value = ExecuteExcel4Macro("'X:\風管報表\交換部位評價表\[IRS部位評價表" & x.Offset(, -3).Value & ".xls]部位評價表'!R1C13")
  6.     Next
  7.   End With
  8. End Sub
複製代碼
作者: jasonwu0114    時間: 2013-12-19 14:50

回復 9# stillfish00

stillfish00大大
是的沒錯
非常感謝
學到不少東西
作者: bqwangs    時間: 2014-1-7 15:06     標題: EXCEL抓取CSV資料並即時更新

我的檔案內容如下 是CSV格式
[attach]17204[/attach][attach]17204[/attach]
5個資訊 我用顏色顯示 比較容易讓各位大大了解我的意思
小型台指月   奇狐_試用   2013/12/26 12:17:00   8500   1   [attach]17204[/attach][attach]17204[/attach][attach]17204[/attach][attach]17204[/attach]

我想要用一個EXCEL檔
將上面的5個資訊
分別載入至EXCEL的 A1-E1個別儲存格(1個儲值格塞1個資訊)
並會一直更新
也就是
只要CSV的資料有變動
EXCEL也會馬上跟著變動

要有上述此種功能 VBA語法要如何寫
作者: bqwangs    時間: 2014-1-7 15:26

回復 1# bqwangs
主要是EXCEL檔案會一直開著
但CSV檔案 不會開啟 但內容資訊 會有更新

所以希望EXCEL抓取CSV資訊時
CSV是免開檔的方式抓取或隱式開啟的方式

期待各位大大的教導
作者: stillfish00    時間: 2014-1-7 16:24

回復 2# bqwangs
資料>取得外部資料>從文字檔
作者: GBKEE    時間: 2014-1-7 17:22

回復 12# bqwangs
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Ar
  4.     Ar = ExecuteExcel4Macro("'d:\[tttt.csv]tttt'!R1C1")
  5.     'd:\  請修改為正確路徑
  6.     Ar = Split(Trim(Ar), Space(2))
  7.     With ActiveSheet.[a1:e1]
  8.         .Value = Ar
  9.         .Value = .Value
  10.     End With
  11. End Sub
複製代碼
作者: bqwangs    時間: 2014-1-7 20:40

本帖最後由 bqwangs 於 2014-1-7 20:41 編輯

超級版主
我的版本是2010的
我試了
不行
是我不會弄
可以幫忙做一個檔案嗎
供我下載
作者: GBKEE    時間: 2014-1-8 07:47

回復 15# bqwangs
14# 的程式碼,可不開檔讀取資料,2003可正常執行.

另幕後開檔讀取資料,可參考
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Ar
  4.     'd:\  請修改為正確路徑
  5.     With GetObject("d:\tttt.csv").Sheets(1)
  6.         Ar = Split(Trim(.[a1]), Space(2))
  7.         .Parent.Close 0
  8.     End With
  9.     With ActiveSheet.[a1:e1]
  10.         .Value = Ar
  11.         .Value = .Value
  12.     End With
  13. End Sub
複製代碼
作者: bqwangs    時間: 2014-1-8 11:11

本帖最後由 bqwangs 於 2014-1-8 11:14 編輯

回復 16# GBKEE

版大
我依您的方式
弄了一個檔
[attach]17219[/attach][attach]17219[/attach]

他開啟時 需去按巨集 再按執行
才會抓取資料
但要如何 讓檔案開啟時
即自動抓資料 也會一直更新(只要CSV檔變動 EXCEL儲存格資料 也跟著變動)
類似同步

之前資料擷取儲值格位置是A1-E1
但如果我要改成A1 A2 B5 E5 C5
那又要如何改??
作者: GBKEE    時間: 2014-1-9 10:15

回復 17# bqwangs
你說 只要CSV檔變動 EXCEL儲存格資料 也跟著變動
用CSV檔無法更新連結
作者: bqwangs    時間: 2014-1-9 10:34

版大
但我試著用 按巨集 再按執行
他會一直更新跟csv檔案一樣的資訊
有辦法改成 每秒 自動作  按巨集 再按執行1次嗎??

之前資料擷取儲值格位置是A1-E1
但如果我要改成A1 A2 B5 E5 C5
那又要如何改??
作者: GBKEE    時間: 2014-1-9 11:08

回復 19# bqwangs
  1. Option Explicit
  2. Sub Ex()
  3.     Dim Ar As Variant, E As Range, i As Integer
  4.     'd:\  請修改為正確路徑
  5.     With GetObject("d:\tttt.csv").Sheets(1)
  6.         Ar = Split(Trim(.[a1]), Space(2))
  7.         .Parent.Close 0
  8.     End With
  9.     i = 0
  10.     For Each E In ActiveSheet.[A1,A2,B5,E5,C5]
  11.         E = Ar(i)
  12.         i = i + 1
  13.     Next
  14.     Application.OnTime Time + #12:00:01 AM#, "工作表1.Ex"
  15.     '程式在物件模組 OnTim 要指明 模組名稱

  17. End Sub
複製代碼
作者: bqwangs    時間: 2014-1-9 14:14

版大
我是程式大外行
1.上述的程式 是放到工作表1的VB裡
還是放到Module1嗎??
2.Application.OnTime Time + #12:00:01 AM#, "工作表1.Ex"
'程式在物件模組 OnTim 要指明 模組名稱
是要改成??
Application.OnTime Time + #12:00:01 AM#, "Module1.Ex"
3.我將程式放到工作表1的VB裡
還是要去 按巨集 再按執行
要如何變成一開檔案 就馬上會執行

=============
This Workbook裡
Private Sub Workbook_Open()
Ex
        End Sub
=============
Module1裡放程式
Sub Ex()
    Dim Ar As Variant, E As Range, i As Integer
    'd:\  請修改為正確路徑
    With GetObject("d:\tttt.csv").Sheets(1)
        Ar = Split(Trim(.[a1]), Space(2))
        .Parent.Close 0
    End With
    i = 0
    For Each E In ActiveSheet.[A1,A2,B5,E5,C5]
        E = Ar(i)
        i = i + 1
    Next
    Application.OnTime Time + #12:00:01 AM#, "工作表1.Ex"
    '程式在物件模組 OnTim 要指明 模組名稱
=====================
錯誤訊息
[attach]17225[/attach]
作者: GBKEE    時間: 2014-1-9 15:50

回復 21# bqwangs
  1. Application.OnTime Time + #12:00:01 AM#, "工作表1.Ex"
  2. 15.    '程式在物件模組 OnTim 要指明 模組名稱 (工作表1 是物件模組)
複製代碼
  1. 'This Workbook是 (物件模組)
  2. Private Sub Workbook_Open()
  3. Ex
  4.         End Sub
  5. =============
  6. 'Module1裡放程式(Module1是一般模組)
  7. Sub Ex()   
  8.     Dim Ar As Variant, E As Range, i As Integer
  9.     'd:\  請修改為正確路徑
  10.     With GetObject("d:\tttt.csv").Sheets(1)
  11.         Ar = Split(Trim(.[a1]), Space(2))
  12.         .Parent.Close 0
  13.     End With
  14.     i = 0
  15.     For Each E In ActiveSheet.[A1,A2,B5,E5,C5]
  16.         E = Ar(i)
  17.         i = i + 1
  18.     Next
  19.     Application.OnTime Time + #12:00:01 AM#, "Ex"
  20.     '程式在物件模組 OnTim 要指明 模組名稱,(Module1是一般模組 OnTim可以不指明 模組名稱)
複製代碼




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)