- 帖子
- 79
- 主題
- 33
- 精華
- 0
- 積分
- 123
- 點名
- 0
- 作業系統
- win7
- 軟體版本
- x64
- 閱讀權限
- 20
- 性別
- 男
- 來自
- 台灣
- 註冊時間
- 2013-10-24
- 最後登錄
- 2024-3-28
|
如何間接抓取其它檔案的資料？
這是一個月結 開發票的程式 雛形 （一些不適合的資料我都先拿掉了，不討論適法性的問題）
因為客人都是簽帳月結，所以每個月的月底都要花很長的時間開發票，我想讓這個程序更快，所以著手用 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)
|
|
發表於 2017-4-11 11:34
| 
