返回列表 上一主題 發帖

[發問] 儲存格索引值

[發問] 儲存格索引值

各位先進好,

想請教一個VBA的寫法
我將程式所在的儲存格
B1設為要執行的檔案名稱
B2設為工作表名稱,且以數字命名
B3設為從第N個sheet開始起算
B4設為一個日期的值
問題:
以下程式寫法,只能索引到要執行的檔案去,其他都不起作用,
例如:
B2本來是指向特定工作表,但因為以數字命名,就被當成是第n個工作表,老是跑錯sheet
B3則還寫不出來
B4所設定的日期,則無法貼上指定的sheet

請問以下程式該如何修改?
  1. Sub 測試()
  2. Dim Wb As Workbook, Sh As Worksheet, x As Range
  3.     Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1").Value) '儲存格的值為檔案名稱
  4.     Set Sh = Wb.Worksheets(Worksheets("VBA").Range("B2").Value) '儲存格的值為工作表名稱
  5.     Sh.Activate
  6.     With Sh
  7.     Set x = .Range("B4")
  8.     x = Range("B4").Value '指定1.sheet日期
  9.     End With
  10. End Sub
複製代碼

工作表

20190421.161842.jpg
2019-4-21 16:20

回復 1# PJChen


    請問妳的這支程序主要目的為何?
    是要將指定工作表依據日期來做命名嗎?

   看不懂妳的B2、B3及B4設定,故此詢問。

TOP

回復 1# PJChen

我也不是很了解檔案的關聯性
所以將sh改為字串,就可以指向所設定名稱的sheet

Sub 測試()
Dim Wb As Workbook, sh As String, x As Range
'   Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1").Value) '儲存格的值為檔案名稱
    sh = Worksheets("VBA").Range("B2") '儲存格的值為工作表名稱
    Worksheets(sh).Activate
    With Worksheets(sh)
    Set x = .Range("B4")
    x = Worksheets("VBA").Range("B4").Value '指定1.sheet日期
    End With
End Sub

TOP

回復 1# PJChen

Set Sh = Wb.Worksheets(Worksheets("VBA").Range("B2").Value) '儲存格的值為工作表名稱

改為
Set Sh = Wb.Worksheets(Worksheets("VBA").Range("B2").Text) '儲存格的值為工作表名稱

TOP

回復 4# jcchiang

謝謝你,改為text真的就可以了!
剩下第4個問題還沒解決
可以的話,也請指導一下,謝謝                測試.rar (40.88 KB)

B6為指定資料夾名稱A
裡面有N個檔案
每個檔案的工作表都以數字命名,例:1…2…3…
程式寫在Macro.xlsm中,
我想指定的工作表名稱放在B2欄位
而要寫人的值放在B4欄位
指定寫入的儲存格為放在B2欄位
我把所要做的事都放在Macro.xlsm的工作表VBA中,
只要從這裡修改值,就可以幫我寫入資料
請問要如何寫這段VBA?可以讓它依序寫入指定的資料夾A?

TOP

回復 5# PJChen

功力沒很好,請試試
資料夾設定為A,如要變動請自行修改(含儲存路徑)

Sub 測試()
Dim Wb As Workbook, Sh As Worksheet, x As Range, Source1 As String
    Source1 = Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1").Value '要開啟的檔案名稱
    Workbooks.Open "c:\A\" & Source1 & ".xlsx" '開啟要寫入的檔案   
    Workbooks("Macro.xlsm").Activate   
    Set Wb = Workbooks(Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1").Value) '儲存格的值為檔案名稱
    Set Sh = Wb.Worksheets(Worksheets("VBA").Range("B2").Text)  '儲存格的值為工作表名稱,因為工作表名稱是數字,會被當成"數第N個工作表",還無法解決,以加1先暫用      
    Sh.Activate
    With Sh
    Set x = Sh.Range(Workbooks("Macro.xlsm").Worksheets("VBA").Range("B5").Text) '指定寫入的儲存格為放在那個欄位
     x = Workbooks("Macro.xlsm").Worksheets("VBA").Range("B4").Text '寫人的值放在那個欄位
    End With
    Workbooks(Dir("C:\A\" & Source1 & ".xlsx")).Close True '關閉並儲存寫入的檔案
End Sub

TOP

回復 6# jcchiang

您好,

Workbooks("Macro.xlsm").Worksheets("VBA").Range("B1")儲存格雖有指名檔名,但是給其他程式用的,
在這個程式中,我是想依序寫入指定的值到A資料夾的所有檔案中(不包含子資料夾),所以不能用指名檔名方式.

TOP

Sub 測試()
Dim P$, S$, T$, R$, F$, N&, xA As Range, xB As Workbook, xS As Worksheet
P = [B6]: If P = "" Then MsgBox "**子資料夾未輸入! ": Exit Sub
P = ThisWorkbook.Path & "\" & P & "\"
If Dir(P, vbDirectory) = "" Then MsgBox "**子資料夾不存在! ": Exit Sub
S = [B2]: If S = "" Then MsgBox "**工作表名稱未輸入! ": Exit Sub
T = [B4].Text: If S = "" Then MsgBox "**寫入的內容未輸入! ": Exit Sub
R = [B5]: If R = "" Then MsgBox "**寫入位址未輸入! ": Exit Sub
On Error Resume Next: Set xA = Range(R): On Error GoTo 0
If xA Is Nothing Then MsgBox "**寫入位址不正確! ": Exit Sub
If xA.Count > 1 Then MsgBox "**寫入位址不是單一儲存格! ": Exit Sub
'--------------------------------------------------------------------
[F1:H999].ClearContents:  Set xA = [F1]
Application.ScreenUpdating = False
Do
   If F = "" Then F = Dir(P & "*.xls") Else F = Dir()  'xls 自行改成 xlsx  
   If F = "" Then Exit Do
   N = N + 1: xA = N: xA(1, 2) = F: xA(1, 3) = "(X)工作表不存在"
   Set xB = Workbooks.Open(P & F):  Set xS = Nothing
   On Error Resume Next: Set xS = xB.Sheets(S): On Error GoTo 0
   If Not xS Is Nothing Then xS.Range(R) = T: xB.Save: xA(1, 3) = "(V)寫入完成"
   Set xA = xA(2):  xB.Close 0
Loop
End Sub

測試(1).rar (23.65 KB)


>>>>
EXCEL參考資料:
http://blog.xuite.net/smile1000mile/blog

TOP

回復 8# 准提部林

大大好,
我改了檔名,程式在Macro.A無法執行,試了很多次都不行!可是貼在正式版又可以,用你的檔案也可以,我查不出原因,可否幫忙看一下?
不過就那次執行有些問題:
1. Macro.A是我用來測試的,正式版的裡面有很多的VBA,這個程式執行後,會幫我貼上
佳乳-全台.xlsx
比菲多-OK.xlsx
比菲多-全台.xlsx
這三個檔"(V)寫入完成",在F:H,而且明明只有三個檔,它卻貼了很多次,一直循環很長一串至168列,
這會把我正式版的Macro.xlsm的版面破壞掉,所以我把寫入的功能暫時取消,
2. 當資料夾不是在Macro.xlsm的同一個地方,而是其它路徑時,改成以下是否正確?
例如 W:\倉儲共用\C,改寫路徑 P = .Path & "W:\倉儲共用\C &"\"
測試3.rar (41.64 KB)

TOP

回復 9# PJChen


哈囉PJ,

我不清楚妳實際置入程序的EXCEL選擇為何以及資料夾位置所在處,但我是將MACROA以及資料夾放在同一路徑下,並在此前提下測試准大的程序是可行的。
未發生妳所提的循環很長至168列。
建議妳可進入程序當中,將每一個變數都加入新增監看式,逐行觀看,看是哪一個地方開始,有非預期的情況發生。(如圖片所視)
程序裡頭新增監看式.png
2019-5-1 15:01


P.S.我有在准大程序裡頭,備註一下各行意思供妳以及其他人參考(未更動准大的程序)。

    Macro.A_加入備註供參考.rar (21.94 KB)

TOP

        靜思自在 : 是非當教育,讚美作警惕。
返回列表 上一主題