Board logo

標題: [發問] 使用儲存格設定來指定活頁 [打印本頁]

作者: 蘿蔔泥    時間: 2021-12-8 15:23     標題: 使用儲存格設定來指定活頁

各位大大好

我有兩個檔案,
一個是"1.年度訓練總表-109",後面的109會隨年度不同而改變
另一個"2.年度訓練計畫表"
"2.年度訓練計畫表"會去抓"1.年度訓練總表-109"的資料來做整理,

目前我有重新修改一下巨集"完成_總結new()"
在"2.年度訓練計畫表"有做一個設定頁,變更年度會開啟那個年度的資料
但要執行Do While的時候我還是要一個個修改成我要的年度
舉例之前是"1.年度訓練總表-108"我就要進去改成"1.年度訓練總表-109"
這個是不是也能參照設定頁的資訊呢?

另外想請問有辦法不打開活頁簿就能抓取資料出來嗎?

巨集寫得有點亂還請見諒
  1. Sub 完成_總結new()

  2. i = 2
  3. If Range("a2") = "" Then
  4.     r = 2
  5. Else
  6.     r = Range("a1").End(xlDown).Row + 1
  7. End If

  8. Workbooks.Open Filename:=Sheets("設定頁").Range("B5")

  9. Do While Range("a" & i) <> ""
  10.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 1) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("a" & i)
  11.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 2) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("e" & i)
  12.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 3) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("f" & i)
  13.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 4) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("j" & i)
  14.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 5) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("i" & i)
  15.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 6) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("d" & i)
  16.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 7) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("l" & i)
  17.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 8) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("m" & i)
  18.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 9) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("q" & i)
  19.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 10) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("t" & i)
  20.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 11) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("ab" & i)
  21.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 12) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("y" & i)
  22.     Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 13) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("z" & i)
  23. '    Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 14) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("aa" & i)
  24.         If Workbooks("1.年度訓練總表-109").Sheets("總表").Range("j" & i) = "外訓" And Workbooks("1.年度訓練總表-109").Sheets("總表").Range("q" & i) <> "" Then
  25.             Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 14) = "詳如明細"
  26.         Else
  27.             Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 14) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("aa" & i)

  28.         End If
  29.     r = r + 1
  30.     i = i + 1
  31. Loop

  32. Workbooks("1.年度訓練總表-109").Close

  33. End Sub
複製代碼

作者: samwang    時間: 2021-12-8 16:28

回復 1# 蘿蔔泥

這個是不是也能參照設定頁的資訊呢?
>> 這是什麼意思? 你的設定頁B1已有年度,這不是很好嗎?
另外也可以將2個檔案同時放在同一個資料夾,這樣就不用設定年度,可以直接開啟另一個檔案


另外想請問有辦法不打開活頁簿就能抓取資料出來嗎?
>> 可以開啟檔案後資料裝入陣列就關閉檔案,然後最後做資料處理,這樣應該很快才對。
作者: 蘿蔔泥    時間: 2021-12-8 17:14

回復 2# samwang


這個是不是也能參照設定頁的資訊呢?
>> 這是什麼意思? 你的設定頁B1已有年度,這不是很好嗎?>>不好意思,我不太懂你的意思
另外也可以將2個檔案同時放在同一個資料夾,這樣就不用設定年度,可以直接開啟另一個檔案
>>是放在同一個年度的資料夾裡,
我寫不出來的是 Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 1) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("a" & i)
要如何讓Workbooks("1.年度訓練總表-109")跟著設定頁變動


另外想請問有辦法不打開活頁簿就能抓取資料出來嗎?
>> 可以開啟檔案後資料裝入陣列就關閉檔案,然後最後做資料處理,這樣應該很快才對。
>>不好意思,請我該怎寫呢?
作者: samwang    時間: 2021-12-9 09:28

回復 3# 蘿蔔泥

是放在同一個年度的資料夾裡,
我寫不出來的是 Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 1) = Workbooks("1.年度訓練總表-109").Sheets("總表").Range("a" & i)
要如何讓Workbooks("1.年度訓練總表-109")跟著設定頁變動
>> 2個檔案放在同一個資料夾,只要開啟"2.年度訓練計畫表"就會自動抓取"1.年度訓練總表-109"資料,請測試看看,謝謝
作者: 蘿蔔泥    時間: 2021-12-13 09:29

回復 4# samwang


  您好,因為我的資料夾裡面不只一個excel檔,可以用指定的方式嗎?
  Workbooks("1.年度訓練總表-109")-->>這個能隨設定頁變動嗎?
作者: samwang    時間: 2021-12-13 10:02

回復  samwang
您好,因為我的資料夾裡面不只一個excel檔,可以用指定的方式嗎?
  Workbooks("1. ...
蘿蔔泥 發表於 2021-12-13 09:29


改用選擇檔案方式,請測試看看,謝謝
Sub test()
Dim FileOrg$, fc%, wb, a, Dr, Sr, j%
Application.ScreenUpdating = False: Application.DisplayAlerts = False
FileOrg = ActiveWorkbook.Name
Sheets("連結總表").[a1].CurrentRegion.Offset(1) = ""
With Application.FileDialog(msoFileDialogOpen)
    .InitialFileName = "D:\"
    .Title = "===========選擇  ""年度訓練總表""  檔案==========="
    .AllowMultiSelect = False: .Show
    fc = .SelectedItems.Count: If fc = 0 Then Exit Sub
    a = .SelectedItems(1)
    Set wb = Workbooks.Open(a)
    With Sheets("總表")
        If .FilterMode Then .ShowAllData
        Set xA = .[a1]
        R = xA(Rows.Count, 1).End(xlUp).Row
        Dr = Array(1, 5, 6, 10, 9, 4, 12, 13, 17, 20, 28, 25, 26, 27)   '資料來源
        Sr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14)       '順序
        For j = 0 To UBound(Dr)
            Workbooks(FileOrg).Sheets("連結總表").Cells(2, Sr(j)).Resize(R).Value = xA(2, Dr(j)).Resize(R).Value
        Next
    End With
End With
wb.Close
Set wb = Nothing:  Set xA = Nothing
Application.ScreenUpdating = True: Application.DisplayAlerts = True
End Sub
作者: 蘿蔔泥    時間: 2021-12-14 14:56

回復 6# samwang


好的,謝謝,最近比較忙我會再找時間測試
另外想請問Workbooks("1.年度訓練總表-109")-->>這個能隨設定頁變動嗎?
作者: samwang    時間: 2021-12-14 15:25

回復  samwang
好的,謝謝,最近比較忙我會再找時間測試
另外想請問Workbooks("1.年度訓練總表-10 ...
蘿蔔泥 發表於 2021-12-14 14:56


另外想請問Workbooks("1.年度訓練總表-109")-->>這個能隨設定頁變動嗎?
>> 不太能了解您的意思,可舉例一些範例嗎? 謝謝
作者: 蘿蔔泥    時間: 2021-12-30 17:09

請問是哪裡出錯了嗎?一直出現執行錯誤
  1. Sub 完成_總結new()

  2. i = 2
  3. If Range("a2") = "" Then
  4.     r = 2
  5. Else
  6.     r = Range("a1").End(xlDown).Row + 1
  7. End If

  8. SNAME = Sheets("設定頁").Range("B5")
  9. Workbooks.Open Filename:=SNAME

  10. Do While Range("a" & i) <> ""
  11.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 1) = Workbooks("SNAME").Sheets("總表").Range("a" & i)
  12.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 2) = Workbooks("SNAME").Sheets("總表").Range("e" & i)
  13.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 3) = Workbooks("SNAME").Sheets("總表").Range("f" & i)
  14.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 4) = Workbooks("SNAME").Sheets("總表").Range("j" & i)
  15.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 5) = Workbooks("SNAME").Sheets("總表").Range("i" & i)
  16.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 6) = Workbooks("SNAME").Sheets("總表").Range("d" & i)
  17.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 7) = Workbooks("SNAME").Sheets("總表").Range("l" & i)
  18.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 8) = Workbooks("SNAME").Sheets("總表").Range("m" & i)
  19.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 9) = Workbooks("SNAME").Sheets("總表").Range("q" & i)
  20.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 10) = Workbooks("SNAME").Sheets("總表").Range("t" & i)
  21.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 11) = Workbooks("SNAME").Sheets("總表").Range("ab" & i)
  22.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 12) = Workbooks("SNAME").Sheets("總表").Range("y" & i)
  23.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 13) = Workbooks("SNAME").Sheets("總表").Range("z" & i)
  24. '    Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 14) = Workbooks("SNAME").Sheets("總表").Range("aa" & i)
  25.         If Workbooks("SNAME").Sheets("總表").Range("j" & i) = "外訓" And Workbooks("SNAME").Sheets("總表").Range("q" & i) <> "" Then
  26.             Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 14) = "詳如明細"
  27.         Else
  28.             Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 14) = Workbooks("SNAME").Sheets("總表").Range("aa" & i)

  29.         End If
  30.     r = r + 1
  31.     i = i + 1
  32. Loop

  33. Workbooks(SNAME).Close
  34. 'x = WorksheetFunction.CountIf(Sheets("連結總表").Range("c:d"), Range("m:m"))
  35. End Sub
複製代碼

作者: samwang    時間: 2022-1-4 11:26

本帖最後由 samwang 於 2022-1-4 11:28 編輯
請問是哪裡出錯了嗎?一直出現執行錯誤
蘿蔔泥 發表於 2021-12-30 17:09


我用之前提供6#程式碼執行結果無誤如圖片,都有符合您的需求,
選檔案-->然後複製資料-->貼到"連結總表"
請再測試看看,謝謝
作者: 蘿蔔泥    時間: 2022-1-5 13:50

回復 10# samwang


您好,謝謝您
共同作業的人員希望先改為以下的方式操作,
因太深的巨集他無法了解且之後他無法做修改,
故我們討論後只好將原本巨集做了簡單調整方便他日後做維護,
但出現執行錯誤,陣列索引超出範圍
因我們找不出錯誤原因為何,
故想請教大大幫忙

您之前所提供的巨集,我會留下作個人使用,謝謝
  1. Sub 完成_總結new()

  2. i = 2
  3. If Range("a2") = "" Then
  4.     r = 2
  5. Else
  6.     r = Range("a1").End(xlDown).Row + 1
  7. End If
  8. MsgBox (r)

  9. SNAME = Sheets("設定頁").Range("B5")
  10. Workbooks.Open Filename:=SNAME

  11. Do While Range("a" & i) <> ""
  12.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 1) = Workbooks("SNAME").Sheets("總表").Range("a" & i)
  13.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 2) = Workbooks("SNAME").Sheets("總表").Range("e" & i)
  14.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 3) = Workbooks("SNAME").Sheets("總表").Range("f" & i)
  15.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 4) = Workbooks("SNAME").Sheets("總表").Range("j" & i)
  16.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 5) = Workbooks("SNAME").Sheets("總表").Range("i" & i)
  17.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 6) = Workbooks("SNAME").Sheets("總表").Range("d" & i)
  18.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 7) = Workbooks("SNAME").Sheets("總表").Range("l" & i)
  19.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 8) = Workbooks("SNAME").Sheets("總表").Range("m" & i)
  20.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 9) = Workbooks("SNAME").Sheets("總表").Range("q" & i)
  21.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 10) = Workbooks("SNAME").Sheets("總表").Range("t" & i)
  22.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 11) = Workbooks("SNAME").Sheets("總表").Range("ab" & i)
  23.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 12) = Workbooks("SNAME").Sheets("總表").Range("y" & i)
  24.     Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 13) = Workbooks("SNAME").Sheets("總表").Range("z" & i)
  25. '    Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 14) = Workbooks("SNAME").Sheets("總表").Range("aa" & i)
  26.         If Workbooks("SNAME").Sheets("總表").Range("j" & i) = "外訓" And Workbooks("SNAME").Sheets("總表").Range("q" & i) <> "" Then
  27.             Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 14) = "詳如明細"
  28.         Else
  29.             Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 14) = Workbooks("SNAME").Sheets("總表").Range("aa" & i)

  30.         End If
  31.     r = r + 1
  32.     i = i + 1
  33. Loop

  34. Workbooks(SNAME).Close
  35. 'x = WorksheetFunction.CountIf(Sheets("連結總表").Range("c:d"), Range("m:m"))
  36. End Sub
複製代碼

作者: rakefrank    時間: 2022-1-10 15:39

先開1.年度訓練總表-109.xlsm
再開2.年度訓練計畫表.xlsm
執行




歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)