Odczyt danych z innych plików lub arkuszy jest na pewno jedną z najczęstszych czynności wykonywanych w Excelu.
Problem
W przypadku arkusza sprawa jest dosyć prosta. Gdy pobieramy dane z innej zakładki i zmienimy jej nazwę – formuła automatycznie nam się odświeży i nowa nazwa zastąpi starą.
Sprawa się komplikuje, gdy chcemy wprowadzić formułę makrem. W takiej sytuacji formułę najczęściej nagrywa się rejestratorem i wkleja do edytora VB. Domyślnie zawiera ona zarówno nazwę skoroszytu jak i arkusza.
Jest to dosyć niebezpieczne i w praktyce bardzo często generuje błąd.
Nazwa zakładkowa może się zmieniać i być inna od tej podanej w treści makra. Możliwe, że akurat w danym dniu raport nie został utworzony z systemu, tylko ręcznie. Osoba przygotowująca go wpisała inną nazwę arkusza i podmieniła pliki na serwerze.
Cały automat w takiej sytuacji bierze w łeb, ponieważ, makro po prostu się wysypie.
Rozwiązanie
Jak można to rozwiązać? Najlepiej sczytać nazwę arkusza do zmiennej i potem ową zmienną wstawić do treści samej formuły.
Jest to rozwiązanie elastyczne, które jest odporne na zmiany w nazwie arkusza, ponieważ akceptuje każdą dozwoloną nazwę.
Private Const msMODUL As String = "MAktualizacja"
Option Explicit
Private Sub SczytajAktualneCeny()
Const sPROC As String = "SczytajAktualneCeny"
Const sPLIK_CENY_NAZWA As String = "Aktualne ceny.xlsm"
Const sPLIK_CENY_SCIEZKA As String = _
"C:\Documents and Settings\Mariusz Jankowski\Desktop\Aktualne ceny.xlsm"
Dim wkbCeny As Workbook ' Plik "Aktualne ceny.xlsm"
Dim sNazwaArkusza As String ' Nazwa "zakładkowa" pierwszego arkusza
Dim sCiagTekstowy As String ' Ciąg potrzebny do wstawienia formuły
'Aktywuj obsługę błędów na starcie
1 On Error GoTo ObslugaBledu
'Otwórz plik z aktualnymi cenami i pobierz do zmiennej aktualną nazwę
2 Set wkbCeny = Workbooks.Open(Filename:=sPLIK_CENY_SCIEZKA, ReadOnly:=True)
3 sNazwaArkusza = wkbCeny.Worksheets(1).Name
'Zdefiniuj ciąg tekstowy potrzebny do wstawienia formuły makrem
4 sCiagTekstowy = "'[" & sPLIK_CENY_NAZWA & "]" & sNazwaArkusza & "'"
'Wstaw formułę makrem
5 With wksRaport.Range("B2:B10")
6 .FormulaR1C1 = _
"=IFERROR(VLOOKUP(RC1," & sCiagTekstowy & "!R1C1:R99999C2,2,FALSE),""brak ceny"")"
'"=IFERROR(VLOOKUP(RC[-1],'[Aktualne ceny.xlsm]04-10-2012 (4)'!R[-1]C1:R[99997]C2,2,FALSE),""brak ceny"")"
7 .Value = .Value
8 End With
'Zamknij plik
9 wkbCeny.Close SaveChanges:=False
Wyjscie:
10 Set wkbCeny = Nothing
11 On Error GoTo 0
12 Exit Sub
ObslugaBledu:
13 MsgBox "Wystąpił błąd nr " & Err.Number & " (" & Err.Description & ")." _
& vbCr & vbCr & "Linia kodu nr " & Erl & " w procedurze """ & _
sPROC & """ modułu """ & msMODUL & """.", vbInformation, "BŁĄD!"
14 Resume Wyjscie
End Sub
Screen
