[發問] 如何間接抓取其它檔案的資料？

eigen

如何間接抓取其它檔案的資料？

這是一個月結 開發票的程式 雛形 （一些不適合的資料我都先拿掉了，不討論適法性的問題）

因為客人都是簽帳月結，所以每個月的月底都要花很長的時間開發票，我想讓這個程序更快，所以著手用 excel 來處理

一、因為月結的明細是從 erp 抓出來（別叫我用 ERP 開發票，我知道  erp 有開發票功能，但是很多歷史背景因素，用erp 的架構無法達到我要的目的）

月底我會從 erp 匯出 『_r_明細_1060325.xls』 這樣的明細檔案

因為檔名不一致（每個月的匯出資料檔名不一樣），所以我在 主程式的 基本資料 sheet ，做了一個按鍵，用來取得/設定 明細的檔案名稱。


二、經過長時間的努力，我定義了兩個名稱來協助取得明細檔的資料（不討論這兩名稱好壞，這已經是即有架構下的結果了，整個程式的都已經完成，目前只是要加強卻無路可走）

DT_AA =INDIRECT(基本資料!$B$16&基本資料!$B$17&"!$E:$E",1)

DT_LST =OFFSET(INDIRECT(基本資料!$B$16&基本資料!$B$17&"!$B$1",1),MATCH(計算機!$A$1,DT_AA)-COUNTIF(DT_AA,計算機!$A$1),,COUNTIF(DT_AA,計算機!$A$1),)

在sheets(計算機)的 A1 =客戶的編號

在sheets(計算機)的 A17 =COUNTIF(DT_AA,$A$1) 計算出明細中，指定的客戶有幾條明細

在sheets(計算機)的 B17 =MATCH($A$1,DT_AA,1) 計算出明細中，指定的客戶套細套後一行在第幾行

在sheets(計算機)的 P1 = 參考日期。

這是計算機上面的四個主要變數

（上述的架構已經定了，很難修改）

在 ThisWorkBook 中
Private Sub Workbook_Open()
'-------------------------------------------------------------
        '設定快速鍵     https://msdn.microsoft.com/zh-tw/library/office/ff197461.aspx
        
        Application.OnKey "%1", "SheetMove"                alt+1

SheetMove定義在 Macro2之中

執行主程式，巨集會將指定的明細檔 同步開啟。


三、簡單講，就是利用 alt+1 在『月結資料』->『計算機』->『三聯資料』->『月結資料』，做loop 切換的動作。

從月結資料，將要處理的客戶資訊 copy 到 計算機

在計算機將資料算完之後，再 copy 到三聯資料

最後再從三聯資料回到月結資料

重復上述，直到將所有月結資料處理完。


在Sub setup_cal() 之中，一開始，我用
                                Sheets("計算機").[p1] = Sheets("計算機").[p1] + 3       '產生出貨日提示
                                If Sheets("計算機").[p1] > 22 Then
                                        Sheets("計算機").[p1] = 1
                                End If

產生一個 1 4 7 10 13...22 1 ... 的『日期提示』，協助開發票的人，決定發票要開在那一天

ex: 提示 10，那就是設去找 售貨明細，最接近 10日的 日期，做為發票的日期。

現在我想讓這個程序『自動化』也就是讓 setup_cal() 能夠自動抓出最接近的日子，不要再經過人工判斷



四、我是真的有分析，只是一實作下去就卡在那動彈不得

pseudo code()

Sheets("計算機").[p1]+ Sheets("基本資料").Range("f8").value = 『提示日期』        '產生提示的日期

for (i= 0~明細總數）{                                                                //找出最接近的兩個日期

        if( 出貨日(i) >『提示日期』) 暫存日u= 出貨日(i)

        else ( 出貨日(i) <『提示日期』) 暫存日d= 出貨日(i); i=明細總數; break;
}

if (暫存日u - 『提示日期』) > (『提示日期』- 暫存日d )  暫存日 =暫存日d （暫存日d 較接近『提示日期』)
else 暫存日 =暫存日u                                                                //決定日期

但是 vba 實際寫起來就不是這麼一回事

Q1
ex:
在sheets(計算機)的 A17 =COUNTIF(DT_AA,$A$1)=COUNTIF(INDIRECT(基本資料!$B$16&基本資料!$B$17&"!$E:$E",1),$A$1)

在sheets(計算機)的 B17 =MATCH($A$1,DT_AA,1)=MATCH($A$1,INDIRECT(基本資料!$B$16&基本資料!$B$17&"!$E:$E",1),1)

我要在 vba 直接call COUNTIF(INDIRECT(基本資料!$B$16&基本資料!$B$17&"!$E:$E",1),$A$1)
                    
                    MATCH($A$1,INDIRECT(基本資料!$B$16&基本資料!$B$17&"!$E:$E",1),1)
                    
失敗，請指教

Q2
在 for loop 中，我要抓出 Sheets("基本資料").[b16] 這個檔案 的 Sheets("基本資料").[b17] 這一sheet 的 某一行的第二個資料

ex:            
Date_tmp_str = Index(Sheets("基本資料").[b16] & Sheets("基本資料").[b17], 2, date_index - date_index_cnt)
Date_tmp_str=index([_r_售貨明細_20170405.xls]Sheet4!$B:$B,7,1)
Date_tmp_str = Sheets("基本資料").[b16] & Sheets("基本資料").[b17] & "!B:B"
Date_tmp_str = Index(Sheets("基本資料").[b16] & Sheets("基本資料").[b17], 2, date_index - date_index_cnt)
Date_tmp_str = Sheets("基本資料").[b16] & Sheets("基本資料").[b17] & "!B" & date_index - date_index_cnt

，請教教，到底該怎麼寫？ 我查到的似乎都是抓同一個檔案下，不同sheet 的方式，我現在要抓的資料，是在特定欄位，所指定的檔案、活頁下。

我不知道怎麼從 VBA 抓明細檔的資料，下一步就走不下去了

請指教，謝謝~~


※下載檔案，開啟，操作一下 ALT+1 比較容易理解

新增資料夾.zip (50.64 KB)

下載次數: 4

2017-4-11 11:31

eigen

回復 1# eigen


    自問自答： 爬了一整天的  excel vba

        Dim wBook As Workbook
        Dim wSheet As Worksheet
        Dim wValue As String
        Dim wIDcnt As Integer
        Dim wIDMax As Integer

        Set wBook = Workbooks(Sheets("基本資料").[b15].value)

        If Not wBook Is Nothing Then  '檔案開啟
                Set wSheet = wBook.Worksheets(Sheets("基本資料").[b17].value)
               
                wValue = wSheet.Range("B" & "11").value

                wIDcnt = Application.CountIf(wSheet.Range("E:E"), Sheets("計算機").Range("a1").value)
                wIDMax = Application.Match(Sheets("計算機").Range("a1").value, wSheet.Range("E:E"), 1)

                wValue = wSheet.Range("B" & wIDMax).value

        End If
終於