Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Zapisywanie pliku, a formuły

joanna92 · Forumowicz Wersja: Win Office 2013 Posty: 17

Cześć,

Mam problem z rapotem zarządczym finansowym. Przedstawia on szereg danych tabelarycznych.

Liczby w nim prezentowane są w dwojaki sposób
1. Wyniki formuł z określonych pól w arkuszu
2. Dane pobierane do określonych komórek z zewnętrznego źródła (baza SQL) za pomocą w tym przypadku nakładki na excela analizy finansowe sage.

Czy jest jakaś możliwość aby zapisać plik w ten sposób aby "usztywnić" dane pobierane z zewnątrz, ale pozostawić formuły, które są w samym pliku?

W chwili obecnej kiedy chcę przesłać plik - muszę wszystko usztywniać, gdyż nie każdy dla którego jest on przeznaczony ma dostęp bezpośrednio do bazy danych z której pobierane są dane. Z drugiej strony jak usztywnię cały arkusz, wówczas traci on sporo na użyteczności, gdyż nie widać co z czego wynika dla odbiorcy.

Ręczne usztywnianie jedynie wybranych komórek każdorazowo jest bez sensu - za dużo z tym by schodziło.

Nie znam się zbytnio na excelu, więc proszę znęcać się :-)

- jeżeli rozwiązanie jest jakieś banalne :-)

umiejead · Wysłany: 22-02-2021, 17:53

Załącz plik:
1-szy arkusz - to co masz,
2-gi arkusz - to co chcesz mieć.
.

joanna92 · Forumowicz Wersja: Win Office 2013 Posty: 17

Przesyłam plik - czerwone arkusze są to te które posiadają odniesienia do bazy danych i w nich należałoby usztywnić same dane pochodzące z zewnętrznych źródeł. Oczywiście pola z linkami do zewnętrznych baz będą dodawane nowe wraz z pojawianiem się nowych projektów w firmie.

Niestety plik ma 1,5 mb, a limit jest do 900 kb - poniżej link do pobrania

[usunięto]

Trochę inicjatywy.
umiejead.
.

Budget_Actual P&L 2021_v.0_24.12.20_Report 012021.part1.rar

Pobierz Plik ściągnięto 4 raz(y) 800 KB

Budget_Actual P&L 2021_v.0_24.12.20_Report 012021.part2.rar

Pobierz Plik ściągnięto 2 raz(y) 613.18 KB

kuma · Wysłany: 23-02-2021, 13:19

Co to znaczy?

joanna92 · Forumowicz Wersja: Win Office 2013 Posty: 17

Formuły, które zawierają odnośniki do danych zewnętrznych zawsze wyglądają tak

='C:\Program Files\Microsoft Office\Office15\LIBRARY\Analizy finansowe.xla'!Konto('ACT_D1 Projects'!A1295;"2021";1;0;"TAK")

Część
='C:\Program Files\Microsoft Office\Office15\LIBRARY\Analizy finansowe.xla'

jest niezmienna i stała dla wszystkich - końcówka to już parametry

Nie wiem czy o to Ci chodziło.

kuma · Wysłany: 23-02-2021, 15:53

O to też chodziło, ale dodatkowo napisałem o podanie arkuszy z "zewnętrznymi" kolumnami, aby nie przeszukiwać całych arkuszy.
Jeśli jest jedna tylko formuła w danej kolumnie to też podaj, np.:

lub w jakiejś innej zrozumiałej formie.

kuma

Jak masz trudności z wyszukaniem kolumn z tymi formułami to przetestuj poniższy kod.
Powinno zadziałać tak jak opisałaś.

Kod:

Sub test_kuma()
Dim cel As Range, rng_frm As Range
Dim i As Long, eror As Long

Application.Calculation = xlCalculationManual
For i = 1 To Sheets.Count
On Error Resume Next
Set rng_frm = Sheets(i).UsedRange.SpecialCells(xlCellTypeFormulas)
eror = Err.Number
On Error GoTo 0
If eror = 0 Then
For Each cel In rng_frm
If InStr(1, cel.Formula, ".xla") Then
cel.Value = cel.Value
End If
Next
End If
Next
Application.Calculation = xlCalculationAutomatic
End Sub

Waldek

Mój WinRar nie otwiera tych plików. No ale to nie ma znaczenia. Problem uważam za wydumany. Odbiorca pliku po jego uruchomieniu może wybrać opcję "Przerwij łącza" i zapisać pod zmienioną nazwą. Wtedy ma do dyspozycji plik oryginalny gdzie widzi formuły więc wie co z czego wynika, oraz plik z wartościami.

kuma

Można tak jak napisałeś. Jednak musi to zrobić jedynie użytkownik przed wysłaniem do odbiorcy, a nie odbiorca (on nie ma dostępu do odwołań zewnętrznych, np. vide plik wysłany przez @joanna92 na nasze forum ).

Są dwa sposoby. Pierwszy podczas otwierania skoroszytu i wybranie opcji 'Aktualizuj', a następnie usunięcie łączy zewnętrznych, drugi, jeśli wybierze się podczas otwierania 'Nie aktualizuj' to po otwarciu wejście w Dane --> Edytuj łącza.

Pozostaje jeszcze, do kompletu, trzeci sposób - VBA podany przeze mnie.

Waldek

Tak jak pisałem mój WinRar nie otwiera tych plików. Ale wielokrotnie dostawałem pliki gdzie były łącza dla mnie niedostępne. Również tu na Forum. Wtedy wykorzystać można "Nie aktualizuj" i "Przerwij łącza". Oczywiście wysłany plik musi być wcześniej zaktualizowany przez nadawcę. Nie da się zaktualizować pliku nie mając dostępu do danych.