[發問] 出口文件_程式需求

PJChen

各位大大好,

請幫忙以下3個程式,  
來源檔的二種工作表,Invoice & Packing List因為是各廠商自行做,命名方式各有不同,
有分開存檔,也有存放在同一檔案中,類型有xlsx & xls

出貨文件_PO_.xlsx
需要匯總(來源檔)"各廠商文件"資料夾中的資料

(來源檔)invoice/CIV/INV...貼CIV 工作表
(來源檔)的PKL/packing list/PK/packing...貼PKL 工作表

需求如下
--------------A 程式一 (貼資料)
1..自動開啟(來源檔)"各廠商文件"資料夾中的檔案,
(來源檔)invoice/CIV/INV...貼CIV 工作表
(來源檔)的PKL/packing list/PK/packing...貼PKL 工作表

2..所有資料都貼上值就好,因為出貨文件_PO_.xlsx中的有設定格式及公式

3..(來源檔)invoice & packing list欄位與 出貨文件_PO_.xlsx
不一定完全相同,所以貼的時候,要比對欄位名稱,避免貼錯

4.. 取資料時表頭/表尾的資料都不要,只要表格中間段就好,
EX:J-CPOMPA0198NV(47)_INV.xlsx 取21~65列

5.. 完成後以CIV工作表Q11 & U11 & B17的名稱另存新檔,格式為xlsx
B17是日期,取YYYYMMDD
EX:CPOMPA0198NV(48)_20230413

--------------B 程式二 (CK)
B1.. CIV & PKL 工作表
1.. U欄取出A欄的左邊的英文字, EX:HU020123NV01,取HU
2.. V欄填入中文廠商名稱(以U欄的代號找到Vender工作表對應的B欄資料)

B2.. PKL 工作表
3.. O欄的箱號,各廠商的標示方法各有不同,有"-"," ~ ","~"," - ",不論起迄號碼用什麼符號隔開,都改為"~",有空格的則不要空格,EX:" ~ "改為"~"

4.. W欄 箱數驗算,計算O欄的箱號,EX:O28為F11~F12=2箱,其計算結果,填入W欄


5.. X欄 比對W & P欄(廠商自行計算),數字是否一致,一致則空白,不一致則在X欄標示"err"
EX:O195為J19～J32,J35～J49,J54～J61,P195箱數顯示為14
W195計算結果是37,與P195= 14不一致,則x195則標示err,

X欄標示err則A:AA用黃色標示,提醒有錯誤

6.. Y欄 空號CK
將O欄有跳號的箱號抓出來,填在Y欄
EX:O27為 F8~F8, O28為 F11~F12,則空號是9~10,標示在Y28
EX:O195為J19～J32,J35～J49,J54～J61,則空號是33~34,50~53,標示在Y195

7..Z欄 驗算Total Cuft 計算方式=M欄*P欄

8..AA欄 ck 1
比對N & Z欄,不相等時,AA欄標示"err"

9.. AB欄 驗算Q'TY 計算方式=H欄*P欄

10.. AC欄 ck 2
比對G & AB欄,不相等時,AC欄標示"err"


--------------C 程式三 shipping mark
將PKL工作表 A欄所有訂單號碼 & O欄的箱號,全部標示在shipping mark工作表
因為shipping mark的資料很多,我只寫了6個範例

標示方法說明如下:
E2        -DITTO- (固定標示字文)
E3        PO#        EX:PO# HU020123NV01
E4        C/NO.  EX:C/NO. C (3,20,118~119,125,153)
大部份箱子編號前面都有英文字,將英文字寫一個即可,數字則在()內

每填入3列,要有2列空白,每欄只填入3組資料

同一廠商,箱子編號英文字相同的,全部列在一起
EX:
PO# J122122NV01/ J011123NV01/ J020823NV01/ J022123NV01/ J122122NV01/ J011123NV01/ J020823NV01
C/NO. L (0001~0129)

程式需求.rar (376.7 KB)

下載次數: 2

2023-4-16 03:43

各廠商文件.rar (1.96 MB)

下載次數: 0

2023-4-16 03:43

PJChen

補充：
這些廠商貼資料沒有特定順序，通常是invoice跟packing list兩份文件貼的順序相同就可以,每次出貨的文件廠商也都不一定。

--------------C 程式三 shipping mark
將PKL工作表 A欄所有訂單號碼 & O欄的箱號,全部標示在shipping mark工作表

標示方法說明如下:
E2        -DITTO- (固定標示字文)
E3        PO#        EX:PO# HU020123NV01....來自於A欄
E4        C/NO.  EX:C/NO. C (3,20,118~119,125,153)....來自於O欄
一定要用程式自己撈出A、O欄資料,雖然廠商也有提供這些,但有時不一定正確！

PJChen

回復 2# PJChen

各位大大好,

感覺一下說明很多,會很難寫程式,也容易弄混,改一小部份執行沒問題後,再進行下個程式！
格式有小小修改,之前檔案請不要用~~

程式一 (貼資料)修改需求如下：

說明：
1. 各廠商文件 是要貼到 出貨文件_PO_.xlsx的CIV / PKL工作表,CIV / PKL工作表是分別作業的,這次先做PKL工作表
2. 廠商的檔案都會存放在各別的資料夾中,資料夾命名與"驗算工作表"的B欄相同,貼資料的順序也與B欄順序相同
3. 若B欄有資料,但各廠商文件中沒有相同資料夾名稱,則略過、跳下一個,直到所有資料貼完
4. 當文件未到齊、跳過的資料,下回要貼時,以同樣方式在空白處繼續貼
5. 來源檔 各廠商文件檔案命名方式,一定包含PKL工作表[Q12]的字串,PKL檔案&工作表,命名方式有許多種,如:PKL/packing list/PK/packing
6. 各廠商文件檔案,大多將PKL & CIV分開存檔,但少部份會存成一個檔案,但工作表一定分開！
7. 所有文件的表頭都在第20列
8. 出貨文件_PO_.xlsx驗算工作表B欄的廠商名稱,排列順序依每次作業要求,都不會完全相同,廠家也都不一定.

程式需求如下:
1..出貨文件_PO_.xlsx驗算工作表的第一個資料夾名稱是HU,自動開啟"各廠商文件/HU"包含"PKL/packing list/PK/packing"字串的檔案

2..將出貨文件_PO_.xlsx PKL工作表第20列的表頭貼到HU資料夾內的"PK工作表"第19列,若程式能有自動偵測功能,可找到表頭,就貼到表頭的上一列,進行比對表頭是否相同?

3..表頭以出貨文件_PO_.xlsx PKL工作表第20列為準,當各廠商文件的PK工作表,表頭不相符時,則新增一欄,將欠缺的表頭補上,直到表頭格式完全相同,案例寫在HU資料夾內的"PK工作表

4..在HU資料夾內的"PK工作表[R:T]寫入驗算公式,且要保留公式

5.. HU資料夾內的"PK工作表A欄最後一列有SUB TOTAL,是合計列,[R:T]驗算公式,要在這列合計,且要保留公式

6.. HU資料夾的PK檔,以原檔名存檔

7.. HU資料夾內的"PK工作表有SUB TOTAL的合計列[H:Q],copy值(不要公式)至出貨文件_PO_.xlsx 驗算 工作表的同資料夾名稱的儲存格HU [H:Q]

8.. 將HU資料夾的PK檔資料[A:Q],copy至 出貨文件_PO_.xlsx PKL工作表[A:Q],由於前面已比對過2個檔案的表頭,所以欄位全部相同,可以直接貼,但只貼上值,勿破壞檔案格式

9.. HU資料夾內的"PK工作表A欄SHIPPING MARK:/MADE IN TAIWAN R.O.C.
這幾列資料,整列copy 至 出貨文件_PO_.xlsx "shipping mark工作表",空2列再貼下一份嘜頭資料

10.. 全部資料都貼完後,將 出貨文件_PO_.xlsx,3個工作表 PKL / shipping mark/ 驗算,以PKL[Q12]儲存格字串+"_PKL",為檔名 另存新檔.xlsx

出貨文件_PO_.rar (379.76 KB)

下載次數: 1

2023-4-18 21:04

各廠商文件.rar (1.99 MB)

下載次數: 1

2023-4-18 21:05

PJChen

回復 3# PJChen
各位大大好,

請問我要以第19列為準,比對第20列的表頭,如何讓它比對後,自行新增缺少欄位?
並補上表頭的文字?

DAJ.rar (18.36 KB)

下載次數: 1

2023-4-21 19:14

PJChen

回復 4# 准提部林

准大好,

我有多個檔案(上傳2個做測試),要貼到出貨文件_PO_簡.xlsm的PKL工作表,只貼上值就好
我找到一個之前您寫的程式,比較符合我這個程式的需求,但有些地方不知道怎麼修改,您可否幫忙看一下？~~感謝您

Sub PKL_對應欄位貼資料()
'Sheets("PKL")保留列20的標題,去抓取廠商PK的對應欄位,並貼上資料
Dim MyBook As Workbook, MySht As Worksheet, xR As Range, xF As Range
Dim FN$, xB As Workbook, xArea As Range
Application.ScreenUpdating = False
Set MyBook = ThisWorkbook '程式本檔
FN = "代入檔案名稱"
On Error Resume Next: Set xB = Workbooks(FN): On Error GoTo 0 '檢查PK檔是否已開啟
If xB Is Nothing Then Set xB = Workbooks.Open(MyBook.Path & "\" & FN) '若PK檔未開啟, 開啟之
Set xArea = xB.Sheets(1).UsedRange '設定PK檔資料範圍為range物件
'這裡需改成表頭下一列~A欄的第一個空白的上一列(即copy有資料的部份就好,不要包含空白列)
'------------------------------------
Set MySht = MyBook.Sheets("PKL") '本檔資料工作表
MySht.UsedRange.Offset(1, 0).ClearContents '裡需改成清除 標題行下一列~SUB TOTAL上一列
'以下需改成從表頭下一列開始貼,但因為有多份資料,每次貼時,都要從A欄找到的第一個空白處開始貼
For Each xR In Range(MySht.[A1], MySht.Cells(1, Columns.Count).End(xlToLeft))
    Set xF = xArea.Rows(1).Find(xR, Lookat:=xlWhole) '逐一尋找csv第一行符合標題文字的位置
    If xF Is Nothing Then GoTo 101  '找不到符合時, 略過
    xR.Resize(xArea.Rows.Count).Value = xF.Resize(xArea.Rows.Count).Value '複製整欄資料
101: Next
xB.Close 0
End Sub
PKL貼資料測試.rar (479.52 KB)

下載次數: 0

2023-4-22 17:56

Andy2483

回復 4# PJChen


    謝謝論壇,謝謝前輩發表此帖與範例
後學藉此帖練習VBA,學習方案如下,請前輩參考,請各位前輩指教

執行前:

下載 (101.36 KB)

2023-4-26 11:42

執行結果:

下載 (102.35 KB)

2023-4-26 11:42

Option Explicit
Sub TEST()
Dim Brr, R&, j&, C%, N%
C = Cells(19, Columns.Count).End(xlToLeft).Column
R = Range([A1], ActiveSheet.UsedRange).Rows.Count
Brr = Range([A19], Cells(20, C))
For j = 1 To UBound(Brr, 2)
   If Trim(UCase(Brr(1, j))) <> Trim(UCase(Brr(2, j - N))) Then
      Cells(20, j - N).Resize(R - 19).Insert Shift:=xlToRight
      Cells(20, j - N) = Cells(19, j - N): N = N + 1
   End If
Next
Erase Brr
End Sub

Andy2483

回復 4# PJChen


    謝謝前輩,請參考此帖的學習方案忽略#6樓的學習方案
(因為#6樓只考慮到差1個欄位),後學搞錯邏輯

Option Explicit
Sub TEST()
Dim Brr, R&, j&, C%
C = Cells(19, Columns.Count).End(xlToLeft).Column
R = Range([A1], ActiveSheet.UsedRange).Rows.Count
i01:
Brr = Range([A19], Cells(20, C))
For j = 1 To UBound(Brr, 2)
   If Trim(UCase(Brr(1, j))) <> Trim(UCase(Brr(2, j))) Then
      Cells(20, j).Resize(R - 19).Insert Shift:=xlToRight
      Cells(20, j) = Cells(19, j): GoTo i01
   End If
Next
Erase Brr
End Sub
這應該有更適合的寫法,請各位前輩指教

PJChen

回復 7# Andy2483
大大好,
1. 當19列的欄位>20列時,新增欄位沒問題.
2. 19列的欄位<20列時,雖然19&20欄位可以相符,右邊會有一些多餘欄位,
3. CIV工作表也有執行前後的比較
麻煩您幫忙看下,如何讓19列的欄位<20列時,表格維持正常?
我將item 2執行後的結果,PO上來！
比對欄位_自動增減.rar (107.22 KB)

下載次數: 2

2023-4-26 23:10

Andy2483

回復 8# PJChen


    謝謝前輩回復,謝謝論壇,謝謝各位前輩

1.這次的範例不一樣,情境不一樣,是多出欄位,後學認為應該要要求前流程提供檔案者要自律,提供正確的資料檔
2.因為前輩的實際使用檔通常會有公式,這次範例應該是要刪除兩欄中局部的儲存格
3.而且要考慮2刪除後會不會影響公式,請前輩自己試寫刪除多餘局部儲存格的VBA,有問題再提出

祝 成功

下載 (92.22 KB)

2023-4-27 08:40

PJChen

回復 9#
各位大大好,
程式需求是以第19列為主,比對第20列
原程式19列<20列時,右邊會產生多餘欄數
19>20列,亦相同,原程式移動方式有有誤
雖有修改程式,但只是表面上欄位相同,但表格錯亂
我將表格上色,二種情境執行後的結果po上,請比較~~

是否有能人可幫忙修改??
比對欄位_自動增減.rar (236.47 KB)

下載次數: 3

2023-4-28 23:11