Szukaj…
Skoroszyty aplikacji
W wielu aplikacjach Excel kod VBA wykonuje działania skierowane na skoroszyt, w którym jest zawarty. Zapisujesz ten skoroszyt z rozszerzeniem „.xlsm”, a makra VBA koncentrują się tylko na arkuszach i danych w nim zawartych. Jednak często zdarza się, że trzeba połączyć lub scalić dane z innych skoroszytów lub zapisać niektóre dane w osobnym skoroszycie. Otwieranie, zamykanie, zapisywanie, tworzenie i usuwanie innych skoroszytów jest powszechną potrzebą wielu aplikacji VBA.
W dowolnym momencie w edytorze VBA możesz przeglądać i uzyskiwać dostęp do wszystkich skoroszytów aktualnie otwartych przez to wystąpienie programu Excel za pomocą właściwości Workbooks obiektu Application . Dokumentacja MSDN wyjaśnia to za pomocą odniesień.
Kiedy używać ActiveWorkbook i ThisWorkbook
Najlepszą praktyką VBA jest zawsze określanie, którego skoroszytu dotyczy Twój kod VBA. Jeśli ta specyfikacja zostanie pominięta, wówczas VBA zakłada, że kod jest kierowany do aktualnie aktywnego skoroszytu ( ActiveWorkbook ).
'--- the currently active workbook (and worksheet) is implied
Range("A1").value = 3.1415
Cells(1, 1).value = 3.1415
Jednak gdy kilka skoroszytów jest otwartych w tym samym czasie - szczególnie, zwłaszcza gdy kod VBA działa z ActiveWorkbook Excel - odniesienia do ActiveWorkbook mogą być mylone lub przekierowywane. Na przykład dodatek z UDF, który sprawdza porę dnia i porównuje go z wartością zapisaną w jednym z arkuszy roboczych dodatku (które zazwyczaj nie są łatwo widoczne dla użytkownika), będzie musiał wyraźnie określić, który skoroszyt jest odniesienie. W naszym przykładzie nasz otwarty (i aktywny) skoroszyt ma formułę w komórce A1 =EarlyOrLate() i NIE ma napisanego VBA dla tego aktywnego skoroszytu. W naszym dodatku mamy następującą funkcję zdefiniowaną przez użytkownika (UDF):
Public Function EarlyOrLate() As String
If Hour(Now) > ThisWorkbook.Sheets("WatchTime").Range("A1") Then
EarlyOrLate = "It's Late!"
Else
EarlyOrLate = "It's Early!"
End If
End Function
Kod UDF jest zapisywany i zapisywany w zainstalowanym dodatku Excel. Wykorzystuje dane przechowywane w arkuszu w dodatku o nazwie „WatchTime”. Gdyby UDF użył ActiveWorkbook zamiast ThisWorkbook , to nigdy nie byłby w stanie zagwarantować, który skoroszyt był przeznaczony.
Otwieranie (nowego) skoroszytu, nawet jeśli jest już otwarty
Jeśli chcesz uzyskać dostęp do skoroszytu, który jest już otwarty, pobranie zadania z kolekcji Workbooks jest proste:
dim myWB as Workbook
Set myWB = Workbooks("UsuallyFullPathnameOfWorkbook.xlsx")
Jeśli chcesz utworzyć nowy skoroszyt, użyj obiektu kolekcji Workbooks aby Add nowy wpis.
Dim myNewWB as Workbook
Set myNewWB = Workbooks.Add
Są chwile, kiedy możesz nie lub (lub przejmować się), czy potrzebny skoroszyt jest już otwarty, czy nie, albo możliwe, że nie istnieje. Przykładowa funkcja pokazuje, jak zawsze zwracać poprawny obiekt skoroszytu.
Option Explicit
Function GetWorkbook(ByVal wbFilename As String) As Workbook
'--- returns a workbook object for the given filename, including checks
' for when the workbook is already open, exists but not open, or
' does not yet exist (and must be created)
' *** wbFilename must be a fully specified pathname
Dim folderFile As String
Dim returnedWB As Workbook
'--- check if the file exists in the directory location
folderFile = File(wbFilename)
If folderFile = "" Then
'--- the workbook doesn't exist, so create it
Dim pos1 As Integer
Dim fileExt As String
Dim fileFormatNum As Long
'--- in order to save the workbook correctly, we need to infer which workbook
' type the user intended from the file extension
pos1 = InStrRev(sFullName, ".", , vbTextCompare)
fileExt = Right(sFullName, Len(sFullName) - pos1)
Select Case fileExt
Case "xlsx"
fileFormatNum = 51
Case "xlsm"
fileFormatNum = 52
Case "xls"
fileFormatNum = 56
Case "xlsb"
fileFormatNum = 50
Case Else
Err.Raise vbObjectError + 1000, "GetWorkbook function", _
"The file type you've requested (file extension) is not recognized. " & _
"Please use a known extension: xlsx, xlsm, xls, or xlsb."
End Select
Set returnedWB = Workbooks.Add
Application.DisplayAlerts = False
returnedWB.SaveAs filename:=wbFilename, FileFormat:=fileFormatNum
Application.DisplayAlerts = True
Set GetWorkbook = returnedWB
Else
'--- the workbook exists in the directory, so check to see if
' it's already open or not
On Error Resume Next
Set returnedWB = Workbooks(sFile)
If returnedWB Is Nothing Then
Set returnedWB = Workbooks.Open(sFullName)
End If
End If
End Function
Zapisywanie skoroszytu bez pytania użytkownika
Często zapisywanie nowych danych w istniejącym skoroszycie za pomocą VBA spowoduje wyświetlenie wyskakującego pytania z informacją, że plik już istnieje.
Aby uniknąć tego wyskakującego pytania, musisz ukryć tego rodzaju alerty.
Application.DisplayAlerts = False 'disable user prompt to overwrite file
myWB.SaveAs FileName:="NewOrExistingFilename.xlsx"
Application.DisplayAlerts = True 're-enable user prompt to overwrite file
Zmienianie domyślnej liczby arkuszy w nowym skoroszycie
„Fabrycznie domyślna” liczba arkuszy utworzonych w nowym skoroszycie programu Excel jest zwykle ustawiona na trzy. Twój kod VBA może jawnie ustawiać liczbę arkuszy w nowym skoroszycie.
'--- save the current Excel global setting
With Application
Dim oldSheetsCount As Integer
oldSheetsCount = .SheetsInNewWorkbook
Dim myNewWB As Workbook
.SheetsInNewWorkbook = 1
Set myNewWB = .Workbooks.Add
'--- restore the previous setting
.SheetsInNewWorkbook = oldsheetcount
End With
