excel-vba
Skoroszyty

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