iT邦幫忙

0

VBA抓取資料該如何自動抓取，抓到沒有檔案為止

vba excel
z22344566 2017-04-17 15:18:08 ‧ 14935 瀏覽

  • 分享至 

  • xImage

各位大神好
請教一下
一個儲存格抓取一個檔案內的值，檔案的檔名為1704170001、1704170002、1704170003……，依此類推，好比A1儲存格抓1704170001檔案，A2抓1704170002檔案，一直抓到檔案的最後一個停住，就不抓取資料了，迴圈沒有跑完資料抓完了也會停止，請問該如何修改，以及抓檔案名稱為170417該如何增加流水號，才能夠達到這需求....謝謝


Sub Getvalue()

    Dim strYear As String
    
        strPath = Excel.ActiveWorkbook.Path & "\" & Format(Date, "yyyy")
        
        Filename = Format(Date, "yymmdd")
        
            For i = 2 To 10
            
                For j = 2 To 11
                
                    Cells(j, i) = "=vlookup(" & Chr(65) & 2 & ",'" & strPath & "\[" & Filename & ".xlsx]工作表1'!$A$2:$E$6,5,FALSE)"
                
                Next j
            
            Next i

End Sub
aeolus0829 iT邦研究生 4 級 ‧ 2017-04-18 08:59:18 檢舉
建議你查一下遞迴，研究看看
觀念很簡單，應用很多，實作不太好懂 (需要多作幾次)
遞迴不管任何程式語言都用得到，是很重要的東西
froce iT邦大師 1 級 ‧ 2017-04-18 11:18:54 檢舉
簡單的判斷最後一筆資料：
i = Range("A:A").End(xlUp).Row(.Column)

不過限制是那張資料表只有那個table，要不然判定會不準。
登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

1
海綿寶寶
iT邦大神 1 級 ‧ 2017-04-18 10:25:15
最佳解答

沒人要寫，我來寫一份
沒測過，使用請小心

Sub Getvalue()
    Dim strYear As String    
    strPath = Excel.ActiveWorkbook.Path & "\" & Format(Date, "yyyy")
    Filename = Format(Date, "yymmdd")
    bRun = true		'用以提前結束迴圈
    nSeq = 0		'檔案序號
    For i = 2 To 10            
        If bRun=false Then
           Exit For
        End If
        For j = 2 To 11
            If bRun=false Then
               Exit For
            End If
            nSeq = nSeq + 1
            sCheck = strPath & "\" & Filename & Format(nSeq, "000000")
            If Dir(sCheck) <> "" Then   '檔案存在, 進行檢查            
               Cells(j, i) = "=vlookup(" & Chr(65) & 2 & ",'" & strPath & "\[" & Filename & ".xlsx]工作表1'!$A$2:$E$6,5,FALSE)"                
            Else                        '檔案不存在，收工
               bRun = false               
            End If
        Next j            
    Next i
End Sub
看更多先前的回應...收起先前的回應...
z22344566 iT邦新手 4 級 ‧ 2017-04-18 11:52:36 檢舉

使用後
沒有反應

謝謝海綿大的指導
我再研究研究
謝謝

z22344566 iT邦新手 4 級 ‧ 2017-04-19 16:34:43 檢舉 
Sub Getvalue0()
    Dim strYear As String
    Dim nSeq As String
    Filename = Format(Date, "yymmdd")
    strPath = Excel.ActiveWorkbook.Path & "\" & Format(Date, "yyyy")
   
    bRun = True     '用以提前結束迴圈
    nSeq = 0           '檔案序號
    For i = 2 To 3
        If bRun = False Then
           Exit For
        End If
        For j = 2 To 3
            If bRun = False Then
               Exit For
            End If
            nSeq = nSeq + 1
            sCheck = strPath & Filename & Format(nSeq, "0000")
            If Dir(sCheck & "*", vbDirectory) <> " " Then    '檔案存在, 進行檢查
               Cells(j, i) = "=vlookup(" & Chr(65) & 2 & ",'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".xlsx]工作表1'!$A$2:$E$6,5,FALSE)"
            Else                        '檔案不存在，收工
               bRun = False
            End If
        Next j
    Next i
End Sub
z22344566 iT邦新手 4 級 ‧ 2017-04-19 16:44:14 檢舉

海綿寶寶

現在程式能夠執行了，只是迴圈他不會自動跳出，抓到沒有的檔案他變跳出來要我選擇檔案

If Dir(sCheck & "", vbDirectory) <> " " Then
在這段把"'改成" "才能執行
如果說是""這樣子他就變抓不到檔案會直接跳出迴圈

求解......

海綿寶寶 iT邦大神 1 級 ‧ 2017-04-19 18:00:39 檢舉

他是檔案不是目錄
用這樣檢查

sCheck = strPath & Filename & Format(nSeq, "0000") & ".xlsx"
MsgBox "[" & sCheck & "]", vbInformation '看檔名是否正確
If Len(Dir(sCheck)) = 0 Then   '不存在
Else  '存在
End If

MS Excel: How to use the DIR Function (VBA)

z22344566 iT邦新手 4 級 ‧ 2017-04-20 10:03:22 檢舉

謝謝海綿寶寶
msgbox真是太好用了
原來只是路徑有誤而以
讓我長知識了
非常感謝
謝謝謝謝謝謝

z22344566 iT邦新手 4 級 ‧ 2017-04-25 14:09:20 檢舉 
Sub Getvalue()
    Dim strYear As String
    Dim nSeq As String
    Filename = Format(Date, "yymmdd")
    strPath = Excel.ActiveWorkbook.Path & "\" & Format(Date, "yyyy") & "\" & Format(Date, "mm") & "\" & Format(Date, "dd")
   
    bRun = True     '用以提前結束迴圈
    nSeq = 0           '檔案序號
    For i = 2 To 2
        If bRun = False Then
           Exit For
        End If
        For j = 2 To 2
            If bRun = False Then
               Exit For
            End If
            nSeq = nSeq + 1
            sCheck = Excel.ActiveWorkbook.Path & "\" & Format(Date, "yyyy") & "\" & Format(Date, "mm") & "\" & Format(Date, "dd") & "\" & Filename & Format(nSeq, "0000") & ".csv"
            MsgBox "[" & sCheck & "]", vbInformation '看檔名是否正確
           If Dir(sCheck & "", vbDirectory) <> "" Then '檔案存在, 進行檢查
               Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]1704250001'!$A$2:$E$6,5,FALSE)"
            Else                        '檔案不存在，收工
               bRun = False
            End If
        Next j
    Next i
End Sub
z22344566 iT邦新手 4 級 ‧ 2017-04-25 14:13:54 檢舉 
Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]1704250001'!$A$2:$E$6,5,FALSE)"

海綿寶寶大大
就是這段中 sheet名稱是就是檔案名稱
我也嘗試修改成

 Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv] Filename &  Format(nSeq,'0000') '!$A$2:$E$6,5,FALSE)"

但也不能執行 請求幫忙... 感謝

z22344566 iT邦新手 4 級 ‧ 2017-04-25 22:31:58 檢舉

我也有將 Filename & Format(nSeq,'0000') 在前面定義為變數
將變數放進去也沒辦法執行 請問還有什麼方法嗎?

海綿寶寶 iT邦大神 1 級 ‧ 2017-04-26 00:11:11 檢舉 
先檢查看看
MsgBox "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv] Filename &  Format(nSeq,'0000') '!$A$2:$E$6,5,FALSE)", vbInformation

或者先回到原點
試試這樣是否可以

Cells(j, i) = "=vlookup(A2,'d:\data\[201704260001.csv]'!$A$2:$E$6,5,FALSE)"

改到可以之後
再逐漸把固定的部份
用變數取代

海綿寶寶 iT邦大神 1 級 ‧ 2017-04-26 00:12:57 檢舉

你如果還是用下面這種方法檢查檔案存不存在
就不必再問我了

If Dir(sCheck & "", vbDirectory) <> ""
z22344566 iT邦新手 4 級 ‧ 2017-04-26 11:24:23 檢舉 


Sub Getvalue0()
    
    Dim strYear As String
    Dim nSeq As String
             
    Filename = Format(Date, "yymmdd")
    strPath = Excel.ActiveWorkbook.Path & "\" & Format(Date, "yyyy") & "\" & Format(Date, "mm") & "\" & Format(Date, "dd")
   
    bRun = True     '用以提前結束迴圈
    nSeq = 0           '檔案序號
    For i = 2 To 2
        If bRun = False Then
           Exit For
        End If
        For j = 2 To 2
            If bRun = False Then
               Exit For
            End If
            
            nSeq = nSeq + 1
            sCheck = Excel.ActiveWorkbook.Path & "\" & Format(Date, "yyyy") & "\" & Format(Date, "mm") & "\" & Format(Date, "dd") & "\" & Filename & Format(nSeq, "0000") & ".csv"
           ' MsgBox "[" & sCheck & "]", vbInformation '看檔名是否正確
        
           If Len(Dir(sCheck)) <> 0 Then '檔案存在, 進行檢查
               Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]1704260001'!$A$2:$E$6,5,FALSE)"
            Else                        '檔案不存在，收工
              bRun = False
            End If
        Next j
    Next i
    
    MsgBox "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv] 1704260001 '!$A$2:$E$6,5,FALSE)", vbInformation
    
    End Sub

海綿寶寶大大
程式是可以執行的
1704260001他是sheet的名稱
我該如何修改讓他能夠跟檔案名稱一樣
我也有將1704260001換成Filename & Format(nSeq, "0000")
Filename & Format(nSeq, "0000")在前面定義成變數
把1704260001換成變數
一樣都不能執行
檢查方法也改過了
麻煩指導 謝謝

海綿寶寶 iT邦大神 1 級 ‧ 2017-04-26 11:44:36 檢舉

其實我看不懂你寫的
前面寫「程式是可以執行的」
後面又寫「一樣都不能執行」

我換選擇題的方式確認一下
現在的問題是那一個

  1. 程式不會執行 Cells(j, i) 那段
  2. Cells(j, i)設定的內容不是你要的
  3. 無法正確判斷檔案是否存在
  4. Sheet的名稱無法設定成 yymmdd0000

還是那個老方法
先回到原點，確定固定值都沒問題之後
再逐漸改成變數

先確定這樣是 ok 的
Cells(j, i) = "=vlookup(A2,'c:\data\[1704260001.csv]1704260001'!$A$2:$E$6,5,FALSE)"
再用
sPath = "c:\data"
sFilename = "1704260001.csv"
接著確定這樣是 ok 的
Cells(j, i) = "=vlookup(A2,'" & sPath & "\[" & sFilename & ".csv]" & sFilename & "'!$A$2:$E$6,5,FALSE)"
接著再把
sFilename 改成 Format(Date, "yymmdd") & Format(nSeq, "0000")
z22344566 iT邦新手 4 級 ‧ 2017-04-26 11:46:48 檢舉

看來我的表達能力真的有點問題............
我的問題是第4點
就是該怎麼把SHEET名稱改成yymmdd0000格式

海綿寶寶 iT邦大神 1 級 ‧ 2017-04-26 11:57:03 檢舉

是第4點還好

Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]1704260001'!$A$2:$E$6,5,FALSE)"

改成

Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]" & Filename & Format(nSeq, "0000") & "'!$A$2:$E$6,5,FALSE)"

試試看

z22344566 iT邦新手 4 級 ‧ 2017-04-26 12:05:19 檢舉

原來我就只是少了兩個""
謝謝.....

海綿寶寶 iT邦大神 1 級 ‧ 2017-04-26 12:10:20 檢舉

我做個編輯的慢動作分解給你看
這個習慣可以節省掉debug的時間
原本是

Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]1704260001'!$A$2:$E$6,5,FALSE)"

把sheet名稱刪掉，加上雙引號,變成

Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]""'!$A$2:$E$6,5,FALSE)"

加上 & 和空格,變成

Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]" &  & "'!$A$2:$E$6,5,FALSE)"

最後再加上變數,變成

Cells(j, i) = "=vlookup(A2,'" & strPath & "\[" & Filename & Format(nSeq, "0000") & ".csv]" & Filename & Format(nSeq, "0000") & "'!$A$2:$E$6,5,FALSE)"
z22344566 iT邦新手 4 級 ‧ 2017-04-26 14:33:55 檢舉

虛心受教了
謝謝 熱心幫助
真是學涯無岸阿........

已經不是第一次被說表達有問題了/images/emoticon/emoticon02.gif

z22344566 iT邦新手 4 級 ‧ 2017-04-26 16:46:56 檢舉

我又有問題了...........

抓出來的值是 #N/A
可是我如果把1704240001.CSV 這檔案開啟之後
xls就會抓到值
這是不是因為要開啟的檔案是csv的關係.....

z22344566 iT邦新手 4 級 ‧ 2017-04-27 09:14:00 檢舉

http://ithelp.ithome.com.tw/upload/images/20170427/2010308833oU5Fmxiq.jpghttp://ithelp.ithome.com.tw/upload/images/20170427/20103088UPS9bEmaI6.jpghttp://ithelp.ithome.com.tw/upload/images/20170427/2010308827gfttn0QW.jpg

就是這種情形.....

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22209 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙