VBA第九讲 之excel文件操作技巧

excel 文件操作技巧

判断文件是否存在，看文件地址的长度，len

Sub ttt4()

If Len(Dir("C:\Users\Sun\Desktop\A.xlsb.xlsm")) = 0 Then ‘dir后面加文件的路径，dir函数可以返回指定路径的文件名称

MsgBox "A profile doesn't exist"

Else

MsgBox "A profile exist"

End If

End Sub

判断文件是否打开，看窗口windows

Sub ttt5()

Dim x As Integer

For x = 1 To Windows.Count

If Windows(x).Caption = "A.xlsb.xlsm" Then '标题caption等于什么什么字符串

MsgBox "窗口已经打开"

Exit Sub

End If

Next x

End Sub

excel文件新建和保存

‘给对象变量赋值，一定要在之前加set，对象是具有属性和方法的对象。

Sub ttt6()

Dim wk As Workbook

Set wk = Workbooks.Add ’注意此处是workbooks.add

wk.Sheets("sheet1").Range("a1") = 125

wk.SaveAs "C:\Users\Sun\Desktop\B.xls"

End Sub

excel文件的打开和关闭

Sub ttt7()

Dim wb As Workbook

Set wb = Workbooks.Open("C:\Users\Sun\Desktop\B.xls")

MsgBox wb.Sheets("sheet1").Range("a1").Value

wb.Close True

End Sub

excel文件的保存和备份

Sub ttt8()

Dim wb As Workbook

Set wb = ThisWorkbook

wb.Save

wb.SaveCopyAs "C:\Users\Sun\Desktop\c.xls"

End Sub

excel文件复制和删除

Sub ttt9()

FileCopy "C:\Users\Sun\Desktop\B.xls", "C:\Users\Sun\Downloads\fuckidiot.xls"

End Sub ‘还可以顺便实现改名的作用

Sub ttt9()

Kill "C:\Users\Sun\Desktop\c.xls"

End Sub