我的程式筆記

1. 改xlsx檔名

Name "D:\myExcel.xlsx" As "myNewExcel"  'way1.改名

1. 複製新檔+取新名

VBA.FileCopy "D:\myExcel.xlsx", "myNewExcel"  'way2.另存新檔

1. 刪除A檔sheet，複製B檔sheet 貼回A檔

Sub main()
	Dim sht0 As Worksheet
	Dim sht1 As Worksheet
	Dim wbk0 As Workbook
	Dim wbk1 As Workbook
	Dim sht0Index As Integer
    
	
	'--保留原檔
	Set wbk0 = ActiveWorkbook
	'--開啟複製檔
	Set wbk1 = Workbooks.Open(new_file_path)
    
	
	'--將複製檔中已刷新的sheet覆蓋原檔的sheet
	Set sht0 = wbk0.Worksheets("mySheetName")
	Set sht1 = wbk1.Worksheets("mySheetName")
	sht0Index = Sht_Find(wbk0, "mySheetName")
    	
	'----way1.delete and copy-paste sheet
	Application.DisplayAlerts = False
	wbk0.Sheets(sht0Index).Delete
	Application.DisplayAlerts = True

	

	sht1.Copy before:=wbk0.Sheets(sht0Index)

End Sub



Function Sht_Find(ByVal wbk As Workbook, ByVal shtName As String) As Integer
    Dim sht As Worksheet
    '
    For Each sht In wbk.Worksheets
        If sht.Name = shtName Then
            Sht_Find = sht.index
            Exit Function
        End If
    Next
    '
    Sht_Find = -1
End Function

 

1. 複製B檔sheet內容回貼A檔sheet

Sub main()
	Dim sht0 As Worksheet
	Dim sht1 As Worksheet
	Dim wbk0 As Workbook
	Dim wbk1 As Workbook
	Dim sht0Index As Integer
    
	
	'--保留原檔
	Set wbk0 = ActiveWorkbook
	'--開啟複製檔
	Set wbk1 = Workbooks.Open(new_file_path)
    
	
	'--將複製檔中已刷新的sheet覆蓋原檔的sheet
	Set sht0 = wbk0.Worksheets("mySheetName")
	Set sht1 = wbk1.Worksheets("mySheetName")
	sht0Index = Sht_Find(wbk0, "mySheetName")

	'----way2.copy & paste
	sht1.Cells.Copy
	sht0.Paste
End Sub



Function Sht_Find(ByVal wbk As Workbook, ByVal shtName As String) As Integer
    Dim sht As Worksheet
    '
    For Each sht In wbk.Worksheets
        If sht.Name = shtName Then
            Sht_Find = sht.index
            Exit Function
        End If
    Next
    '
    Sht_Find = -1
End Function