- 帖子
- 193
- 主題
- 29
- 精華
- 0
- 積分
- 222
- 點名
- 0
- 作業系統
- Windows7
- 軟體版本
- OFFICE2013
- 閱讀權限
- 20
- 註冊時間
- 2015-9-18
- 最後登錄
- 2023-4-29
|
本帖最後由 v03586 於 2016-11-3 06:07 編輯
請想問大大 如下程式碼 如何修改尋找指定的資料表名稱
我有兩個檔案 A檔案是直行檔 B檔案是參考資料來源
B檔案的Excel 名稱不固定 . 但內容的資料表名稱固定 , 如下寫法 只會判斷 B檔案的第一個順位資料表 , 外加判斷第一個資料表 Title 欄位當中的關鍵字
假設資料表順序一調換就無法判斷...想請問大大如何修改即使資料表順序調換也不影響程式判斷呢??
如下程式碼運作是開啟A檔案 與B檔案 , 執行程式後 會將B檔案的資料匯入A檔案指定的工作表欄位
With Workbooks(i).Sheets(1)
If .Range("A" & title_row).Value Like "A*" And .Range("D" & title_row).Value Like "B*" And .Range("E" & title_row) Like "C*"
Set xS = Workbooks(i).Sheets(1)- Dim UType%
- Sub 匯入()
- Dim xFile$, x As New Application, xB As Workbook, xS As Worksheet
- Dim RepSht As Worksheet, xR As Range, xLab As Range, xAddress$
- Set RepSht = Sheets("A")
- Application.ScreenUpdating = False
- RepSht.UsedRange.EntireRow.Delete
- Dim currentPKG, currentDevice, whichFirst As String
- Dim index As Integer
- Dim hasReport As Boolean
- title_row = 1
- hasReport = False
- For i = 1 To Workbooks.Count
- With Workbooks(i).Sheets(1)
- If .Range("A" & title_row).Value Like "A*" And .Range("D" & title_row).Value Like "B*" And .Range("E" & title_row) Like "C*" Then
- Set xS = Workbooks(i).Sheets(1)
- hasReport = True
- i = Workbooks.Count + 1
- End If
- End With
- Next i
- If hasReport = False Then MsgBox "找不到檔案！ ": Exit Sub
- R = xS.Cells(Rows.Count, 1).End(xlUp).Row + 5
- With RepSht
- .Range("A1:A" & R) = xS.Range("D1:D" & R).Value
- .Range("B1:B" & R) = xS.Range("A1:A" & R).Value
- .Range("C1:C" & R) = xS.Range("AH1:AH" & R).Value
- .Range("D1:D" & R) = xS.Range("C1:C" & R).Value
- .Range("E1:E" & R) = xS.Range("AB1:AB" & R).Value
- .Range("F1:F" & R) = xS.Range("E1:E" & R).Value
- .Range("G1:G" & R) = xS.Range("F1:F" & R).Value
- .Range("H1:H" & R) = xS.Range("G1:G" & R).Value
- .Range("I1:I" & R) = xS.Range("J1:J" & R).Value
- .Range("J1:Z" & R) = xS.Range("K1:AA" & R).Value
- .Range("AA1:AA" & R) = xS.Range("AD1:AD" & R).Value
- End With
- End Sub
|
|
發表於 2016-11-3 06:06
| 
