excel能否替換多個活頁簿名稱與多個活頁簿內容

wei9133
個人積分：11分
文章編號：39560420

11分

樓主

2012-11-02 23:53

各位好
目前有一個問題想請問是否能夠解決
使用版本為excel2003
首先我做了一個表格如下
為了讓每一天都有一個表格並且不用做到每天另存新檔(每天的值不一樣)
所以於下方頁籤0101、0102、0103~0107分別代表每一天而每一天的報表內也都有該天的日期
excel能否替換多個活頁簿名稱與多個活頁簿內容

接下來問題來了
第一個月還可以用土法煉鋼的搞31天出來，後面的幾個月呢(目前是一個頁籤一個頁籤的改)
有沒有辦法取代頁籤的名稱呢
0101>0201
0102>0201
目前好像沒有方法可以取代活頁簿名稱的方法
ctrl+h也只能取代該活頁簿的內容

另外一個部分是活頁簿內文的日期部分
當然其內文的固定某個位置會是
2013/01/01
2013/01/02
2013/01/03
但是好像也沒有一次將excel的所有活頁簿的內容全部替換的功能
也就是將上面共7個(其實應該要是31天)活頁簿的2012/01全部替換成2012/02
使其2013/01/01成為2013/02/01接下來只要將多出來的天數刪除即可
請問各位高手這在2003有解嗎?抑或後面的版本有解嗎?
(該表格為預先製作，故不會有其他時間/日期出現，可以直接整個活頁簿替換沒關係，當然可以指定儲存格更好)

感謝

2012-11-02 23:53 發佈

文章關鍵字 excel 活頁簿內容活頁簿名稱

tfanalysis

tfanalysis
個人積分：3分
文章編號：39739771

3分

2樓

2012-11-10 23:27

Step1.
看要幾個sheet，先手動建好

Step2.
建一個巨集如下

要一月就把迴圈改成101~131
要三月就改成301~331

日期格式若不是你要的，請自行研究正則表達式，或用字串去拆

看日期要放在哪一格，改Cells裡的兩個數字就可以了

結果如下

卍卍不可 http://goo.gl/et6ek0

froce

froce
個人積分：77分
文章編號：39766010

77分

3樓

2012-11-12 10:47


Sub Macro1()

    Dim SheetCount As Integer
    Dim DayName As String
    
    DayName = Application.Text(Month(Date), "00") & Application.Text(Day(Date), "00")
    If Sheets(ActiveWorkbook.Sheets.Count).Name = DayName Then
        MsgBox ("已有相同名稱之工作表")
    Else
        Sheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
        SheetCount = Sheets.Count
        Sheets(SheetCount).Name = DayName
    End If
End Sub

方法一：
每天執行一次這個巨集...會自動產生當天日期的工作表...
不過不能調整工作表的順序...要不然會出錯...

froce

froce
個人積分：77分
文章編號：39766610

77分

4樓

2012-11-12 11:13

方法二：
一個月執行一次...
然後自己刪除多餘的部份...


Sub macro2()
    Dim i As Integer, j As Integer
    Dim SheetName As String
    j = 1
    SheetName = Application.Text(Month(Date), "00")
    For i = Worksheets.Count To 30
        Sheets.Add
    Next i
    For j = 1 To 31
        Sheets(j).Name = SheetName & Application.Text(j, "00")
        Sheets(j).Cells(1, 1) = SheetName & Application.Text(j, "00")
    Next j
End Sub

wei9133

wei9133
個人積分：11分
文章編號：39836204

11分

樓主

2012-11-15 1:24

froce wrote:
方法二：一個月執行一...(恕刪)

你好

Sub 修改標籤()
    Dim i As Integer, j As Integer
    Dim SheetName As String
    j = 1
    SheetName = Application.Text(Month(Date), "00")
    For i = Worksheets.Count To 30
        Sheets.Add
    Next i
    For j = 16 To 30
        Sheets(j).Name = SheetName & Application.Text(j, "00")
        Sheets(j).Cells(1, 11) = SheetName & Application.Text(j, "00")
    Next j
End Sub

這個巨集嘗試了用了一下看起來比較是產生當月的活頁簿
用起來好像是"前兩位數"系統月份(我直接改系統時間發現前兩位數會變)
後面的則是"j"的變數
不過我的excel是做好一次15天
而每個內容都訂好了(內容皆相同僅日期不同與標籤)
所以比較希望的是更改頁籤名稱
不過如果不能直接修改頁籤名的的話也沒關係內容我再一個一個複製起來也是可以
但是就必須請問看看有沒有連月份也可以自行設定的方法了

也就是說需產生頁籤時可以自行設定如以下範圍
0101~0115
0116~0131
0201~0215
0216~0228

請問能夠做到嗎?
謝謝指教

========================================================================

剛才發現到這個巨集連日期都填上了
Sheets(j).Cells(1, 11) ;這部分是讓填在哪一格的
那我可能必須要追加年份在最前面了.
也就是頁籤僅需mmdd
但內文要yyyy/mm/dd
也就是顯示2013年1月1日(這個等於要去設置到該格的顯示方式了)
感覺越來越麻煩了= =|||

froce

froce
個人積分：77分
文章編號：39838974

77分

6樓

2012-11-15 9:03

wei9133 wrote:
請問能夠做到嗎?

當然都做的到啊...你這樣要求反而要考慮的少...


Sub macro2()

    Dim i As Integer
    Dim StartPage As Integer, EndPage As Integer, MonthNumber As Integer
    Dim SheetName As String
    YearNumber = InputBox("請輸入年度")
    MonthNumber = InputBox("請輸入月份")
    StartPage = InputBox("請輸入開始日期")
    EndPage = InputBox("請輸入結束日期")
    SheetName = YearNumber & "年" & MonthNumber & "月"

    If EndPage - StartPage < ActiveWorkbook.Sheets.Count Then
        For i = StartPage To EndPage
            Sheets(i).Name = Application.Text(MonthNumber, "00") & Application.Text(i, "00")
            Sheets(i).[A1] = SheetName & i & "日"
        Next i
    Else
        MsgBox ("欲改名工作表數大於目前現有工作表數，無法更名")
    End If

End Sub

如果程式碼看得懂就盡量照自己的需求去改吧...

wei9133

wei9133
個人積分：11分
文章編號：39858978

11分

樓主

2012-11-16 0:01

經過測試....
好用 100分

而且測了一下如果要清空該格的話
Sheets(i).[A1] = ""
這樣就可以了

另外想問一下
StartPage、EndPage
是內置變量嗎?還是你另外宣告的變量
如果我是想把先前作好的excel清空可供用戶輸入的部分
直接修改標籤內文日期就可以改下一年使用


Sub macro3()
Dim i As Integer
    Dim StartPage As Integer, EndPage As Integer
    If EndPage - StartPage < ActiveWorkbook.Sheets.Count Then
        For i = StartPage To EndPage
           Sheets(i).[a3:a7] = ""
           Sheets(i).[c3:c7] = ""
           Sheets(i).[g5:h18] = ""
        Next i
end sub()

這樣會對嗎?檔案在公司...沒回來所以無法直接測試

不過因為怕用戶會動到公式所以我有用保護鎖起來
需要先一個一個活頁簿解鎖嗎?
以上

froce

froce
個人積分：77分
文章編號：39862173

77分

8樓

2012-11-16 8:32

wei9133 wrote:
另外想問一下
StartPage、EndPage
是內置變量嗎?還是你另外宣告的變量

請仔細看一下原始碼...
程式的內部保留字是不需要做宣告(Dim)的...


Sub macro3()

Dim i As Integer    
Dim StartPage As Integer, EndPage As Integer

    If EndPage - StartPage < ActiveWorkbook.Sheets.Count Then
        For i = StartPage To EndPage
           Sheets(i).[a3:a7] = ""
           Sheets(i).[c3:c7] = ""
           Sheets(i).[g5:h18] = ""
        Next i
end sub()

在macro中宣告的變數並不能跨越macro...
所以如果你要直接用StartPage、EndPage這兩個變數是不行的...
不過可以把這兩個變數宣告成全域變數...
把"Dim StartPage As Integer, EndPage As Integer"拿到模組最上方不要被sub包住就行了...

或者是把這段也整合至前面的程式...

另外if後面記得要加end if、end sub後面不用加()...

froce

froce
個人積分：77分
文章編號：39862686

77分

9樓

2012-11-16 9:00

有保護的儲存格是不能修改的...
不過你可以用錄製巨集的方式去找出程式碼...
然後修改...

一樣可以在改工作表名的時候順便改...
可以先解鎖然後改儲存格...最後再鎖起來...

不過excel可以只保護特定的儲存格...
我是建議在做原始表格的時候就設計好...
輸入區不被鎖定...公式區鎖定...

wei9133

wei9133
個人積分：11分
文章編號：39875152

11分

樓主

2012-11-16 18:13

froce wrote:
有保護的儲存格是不能...(恕刪)

你好
我稍微修改了一下腳本


Sub macro2()
    Dim i As Integer
    Dim StartPage As Integer, EndPage As Integer, MonthNumber As Integer
    Dim SheetName As String
    YearNumber = InputBox("請輸入年度")
    MonthNumber = InputBox("請輸入月份")
    StartPage = InputBox("請輸入開始日期")
    EndPage = InputBox("請輸入結束日期")
    SheetName = YearNumber & "年" & MonthNumber & "月"

    If EndPage - StartPage < ActiveWorkbook.Sheets.Count Then
        For i = StartPage To EndPage
            Sheets(i).Name = Application.Text(MonthNumber, "00") & Application.Text(i, "00")
            Sheets(i).[g1] = SheetName & i & "日"
            Sheets(i).[B4:d9] = ""
            Sheets(i).[B13:d15] = ""
            Sheets(i).[E18] = ""
            Sheets(i).[H21:H25] = ""
            Sheets(i).[F28:F30] = ""
            Sheets(i).[h28:h30] = ""
            Sheets(i).[G31] = ""
            Sheets(i).[a34:e39] = ""
        Next i
    Else
        MsgBox ("欲改名工作表數大於目前現有工作表數，無法更名")
    End If

End Sub

但執行後會產生陣列錯誤"執行階段錯誤 '9':陣列索引超出範圍"

偵錯顯示原因為
Sheets(i).Name = Application.Text(MonthNumber, "00") & Application.Text(i, "00")
好像是因為直接從第16個頁籤開始做修改，後面頁籤數量不夠，所以發生錯誤

請問要怎樣修改讓他正常運作呢?
抱歉一開始沒有講清楚是半個月一份，所以必須修改的基本都是第1~16個頁籤
目前解決方法為先複製全部頁籤讓它成為31個再刪除前面的

=================================================================
另一個部份
我用Sheets(i).[a34:e39] = ""清空的部份
有參照到這些格子的公式會變成顯示#VALUE!而不是原本的0
這個有解嗎?
(這倒是不影響使用，純粹只是畫面看起來的觀感不好而已= =|||)

=================================================================
活頁簿方面我的確是只保護公式欄位
不過為了避免被修改到日期所以連日期欄位也一併保護了
腳本跑到保護的欄位就停止，故我有先將所有活頁簿先解鎖，修改好後再鎖上
以上