Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

[Excel] Odczyt danych z plików, odczyt wartości z nazwy plik

kerim36 24 Lut 2011 16:43 9228 44
  • #1 24 Lut 2011 16:43
    kerim36
    Poziom 10  

    Witajcie

    Mam bojowe zadanie do zrealizowania dla jednego ze swoich klientów.

    Stworzyłem już dla niego plik excela (załącznik) i mam problem, ponieważ nie wiem jak zrobić formułę lub kod w VB, żeby numeracja protokołu szła ładnie z automatu. 1/2011, 2/2011 itd.

    Ogólnie jest szablon plik_szablon.xls i on ma być otwierany jako główny, następnie jest uzupełniany ręcznie i zapisywany na dysku, jako plik1.xls.
    Drugi plik jest zapisywany jako plik2.xls
    Trzeci jako plik3.xls , itd.

    Każdy protokół musi być zapisany jako osobny plik, ponieważ na ich podstawie jest generowane zestawienie (osobny plik, ale to już mało istotne)

    Plik po wpisaniu ma być wydrukowany 2x i zapisany na dysku.

    Wydaje mi się, że optymalnym rozwiązaniem byłby przycisk, który po kliknięciu:
    1-drukuje 2x
    2-zapisuje plik wg ustalonej reguly lub użytkonik to ustala
    3-zwiększa nr protokołu o 1 w górę


    Z gory dzięki za wskazówki
    Mirek

    0 29
  • #2 24 Lut 2011 17:16
    Aldrin
    Poziom 22  

    Trzeba przechować w jakimś miejscu licznik utworzonych protokołów - wybieramy komórkę w arkuszu.

    Proponuję utworzyć/zarejestrować makro o nazwie Auto_Open:
    1. zwiększamy wartość licznika o 1
    2. zapisujemy plik (licznik zostanie zapamiętany)
    3. Plik -> zapisz jako (powstanie nowy plik)
    ... 3a) jako nazwę pliku podajemy stały "przedrostek", np. "Zlecenie " & licznik & ".xls"
    4. czyścimy zawartość komórki z licznikiem, bo i tak oryginalna wartość jest przechowywana w pliku-szablonie, którego użytkownik "nie dotyka".

    Dodatek:
    3b) można rozważyć modyfikację nazwy pliku tak, żeby licznik był np. 3-cyfrowy (001, 010, 102). Wtedy kolejno tworzone pliki będą na liście po kolei, a nie: 1, 11, 2, 3, itd.

    Jeśli do pliku-szablonu użytkownik będzie mieć skrót (Windows) lub przycisk na pasku (Excel), to unikniemy sytuacji w której uruchamia nie ten plik co trzeba.

    Do tego można dobudować test (Dir), czy przypadkiem jakimś cudem w tej lokalizacji nie ma już pliku o takiej nazwie, ale to już są działania dodatkowe, choć też bardzo ważne ;)

    kerim36 napisał:
    Wydaje mi się, że optymalnym rozwiązaniem byłby przycisk, który po kliknięciu:
    1-drukuje 2x
    2-zapisuje plik wg ustalonej reguly lub użytkonik to ustala
    3-zwiększa nr protokołu o 1 w górę

    Taka kolejność stwarza możliwość zapisania pliku ZANIM zostanie zmieniona nazwa pliku - część osób od czasu do czasu naciska przycisk ZAPISZ w czasie wprowadzania danych i wtedy te dane zostaną zapisane w szablonie ZANIM użytkownik przejdzie do punktu 2.

    Jeśli chcemy umożliwić użytkownikowi zmianę protokołu (czy inne ponowne otwarcie, bo np. potrzebna jest jeszcze jedna kopia wydruku), to trzeba wstawić warunek np. jeśli komórka z licznikiem jest pusta (wyczyściliśmy ją), to nie podejmuj dodatkowych działań (utworzenie kolejnego protokołu).
    Można też w ogóle makro wstawić w szablon globalny i z jego poziomu uruchamiać dany plik zwiększając licznik przechowywany "w jakimś miejscu". To zależy od warunków ogólnych, np. liczba stanowisk na których ma to działać, żeby nie biegać do każdego stanowiska w celu modyfikacji/ulepszenia makra.

    0
  • #3 24 Lut 2011 17:52
    kerim36
    Poziom 10  

    a jeszcze inaczej:

    czy jest opcja zeby nr protokołu był zapisany w jakimś konkretnym pliku, np *.txt i z niego był odczytywany nr protokołu oraz tam po zapisaniu był dodany o 1 w górę ??

    0
  • Pomocny post
    #4 24 Lut 2011 18:08
    Aldrin
    Poziom 22  

    Można.
    Sposób szybki: jeśli plik zostanie otwarty w trybie Output, to dotychczasowe dane zostaną nadpisane nową treścią, np. numerem bieżącego/następnego protokołu.

    Można tez skorzystać z trybu Append - wtedy mamy możliwość dopisywania kolejnych danych. Może to byc przydatne do szybkiego bilansu, bo dane o dotychczas utworzonych raportach będą od ręki dostępne w takim pliku. Ciut trudniej tylko będzie się dobrać do kolejnego numeru raportu.
    (to tylko przykład zastosowania)

    0
  • #5 24 Lut 2011 18:28
    adamas_nt
    Moderator Programowanie

    Temat ciekawy, więc dołączam.
    Sprawę blokady zapisu szablonu można rozwiązać następująco

    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
      If SaveAsUI = False Then Cancel = True
    End Sub
    Uwaga: Po wklejeniu tego kodu nie można już zapisać pliku, działa tylko zapisz jako i tak trzeba będzie zapisać szablon.
    W związku z powyższym nie będzie można przechowywać Nr protokołu w pliku i pozostaje tylko rozwiązanie opisywane wyżej.

    Sprawę zapisu kopii z drukowaniem można rozwiązać na kilka sposobów. Najprościej byłoby uruchomić w kodzie okno SaveAs z poskładaną już domyślną nazwą pliku (możliwą do zmiany). Bardziej eleganckim rozwiązaniem byłby UserForm, gdzie można dołożyć jeszcze kilka parametrów (ilość kopii, katalog, etc). Po zatwierdzeniu i zapisaniu w dalszej kolejności drukowanie arkusza, zmiana Nr w pliku tekstowym i pobranie go do szablonu z równoczesnym czyszczeniem zakresów i ustawieniem domyślnych opcji. Tu zrezygnowałbym z pól wyboru na rzecz list przez poprawność danych (dwie wartości Tak/Nie). W ten sposób mamy gotowy arkusz do utworzenia kolejnego protokołu.
    Zdaje się, że potrzebne będzie kilka zabezpieczeń (chociażby wspomniany Dir), ale to pewnie wyjdzie "w praniu".

    0
  • #6 24 Lut 2011 19:07
    Aldrin
    Poziom 22  

    adamas_nt napisał:

    Code:
    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
      If SaveAsUI = False Then Cancel = True
    End Sub
    Uwaga: Po wklejeniu tego kodu nie można już zapisać pliku, działa tylko zapisz jako i tak trzeba będzie zapisać szablon.

    Ten kod będzie też w nowym pliku. Po zapisaniu pliku pod nową nazwą (kolejny protokół) nie będzie można później wprowadzić żadnych modyfikacji- nawet poprawić literówek, czy dopisać zapomnianą pozycję?

    0
  • #7 24 Lut 2011 19:14
    adamas_nt
    Moderator Programowanie

    Hmm, racja. Może, nie rezygnując z powyższego zabezpieczenia, spróbować z eksportem arkusza do nowego pliku z nadaniem nazwy?

    0
  • #8 24 Lut 2011 19:52
    Aldrin
    Poziom 22  

    Tu dużo zależy od osób, które będą z tego korzystać.
    Jeśli będą przerażone kolejnym okienkiem z różnymi opcjami do wyboru, to można z nich zdjąć ten ciężar i utworzyć kolejny protokół wg ustalonych wcześniej reguł. Użytkownik wtedy nie ma nic do zrobienia - wszystko jest już gotowe, a my mamy pewność że wszystko działa tak jak powinno.

    Jeśli zaś byłaby potrzeba dodatkowego kształtowania tego procesu i co chwila trzebaby zmieniać ustawienia przy zapisywaniu pliku, to okno z zestawem parametrów będzie świetną pomocą.

    0
  • #9 24 Lut 2011 19:58
    kerim36
    Poziom 10  

    dzieki panowie za odpowiedzi.

    na chwile obecna widze to tak:
    - stworzyc przycisk i po jego kliknieciu
    - wyskakuje opcja "zapisz jako"
    - drukuje 2x
    - dodaje do licznika 1, a licznik jest w jakims jednym konkretnym pliku. *.txt

    stworzyc przycisk potrafie :)
    akcje do "zapisz jako" zapewne znajde na google
    drukowanie juz znalazlem na drukarce domyslnej
    odczyt i dodanie wartosci do pliku nie mam pojecia


    prosze zatem o wsparcie w postaci kodu, jesli nie calosc to przynajmniej czesci

    0
  • #10 24 Lut 2011 20:46
    Aldrin
    Poziom 22  

    kerim36 napisał:
    akcje do "zapisz jako" zapewne znajde na google
    odczyt i dodanie wartosci do pliku nie mam pojecia

    "Zapisz jako" ładnie widać przy rejestracji makra.

    Dim Tresc As String

    'odczyt całego pliku i przypisanie do Tresc
    Open "Scieżka\nasz_plik.txt" For Input As #1
    Tresc = Input$(LOF(1),#1)
    Close #1

    'całość z Tresc jest zapisana do pliku
    Open "Scieżka\nasz_plik.txt" For Output As #1
    Print #1, Tresc
    Close #1

    0
  • #11 24 Lut 2011 20:59
    adamas_nt
    Moderator Programowanie

    Rejestrator nagrywa zapisanie w tle.

    Code:
    ActiveWorkbook.SaveAs
    Nam chodzi o okno dialogowe. Np
    Code:
    Sub makro()
    
      Nr = 1
      nazwa = "plik" & Nr & ".xls"
      Application.Dialogs(xlDialogSaveAs).Show ("c:\" & nazwa)
    End Sub

    0
  • #12 24 Lut 2011 21:13
    kerim36
    Poziom 10  

    zapis i drukowanie rozwiazalem tak:


    Code:
    Sub Przycisk28_Kliknięcie()
    

    sciezka = Application.DefaultFilePath & "\"
    'sciezka = "C:\agata\"
    nazwa = Range("D4").Value
    ZapiszJako = Application.GetSaveAsFilename(InitialFileName:=sciezka & nazwa, fileFilter:="Skoroszyt Excel (*.xls), *.xls")

    ActiveSheet.PrintOut Copies:=2

    End Sub







    i pozostaje mi tylko odczyt z pliku wartosci, wklejenie go do excela, i po wydruku dodanie +1, po czym zapisanie spowrotem do pliku


    prosze o wsparcie

    0
  • #13 24 Lut 2011 21:34
    adamas_nt
    Moderator Programowanie

    Przed

    Code:
    ActiveSheet.PrintOut Copies:=2 
    dopisz
    Code:
    ThisWorkbook.SaveAs ZapiszJako

    0
  • #14 24 Lut 2011 21:36
    kerim36
    Poziom 10  

    ok
    dopisze



    co do odczytu z pliku zrobilem cos takiego:

    Code:
    Dim MyNumber
    
    wiersz = 4

    Open "C:\agata\test.txt" For Input As #1
    Do While Not EOF(1)
        Input #1, MyNumber
        Cells(4, wiersz).Value = MyNumber
        wiersz = wiersz + 1
       
    Loop
    Close #1

    0
  • #15 24 Lut 2011 22:59
    adamas_nt
    Moderator Programowanie

    Pora zacząć upychać wszystko w odpowiednie miejsca. Wypróbuj załącznik (numerowanie z zapisem od Aldrin+Twoje).

    Edit 23:17: zmieniłem załącznik (byk)...

    0
  • #16 25 Lut 2011 16:13
    kerim36
    Poziom 10  

    dzieki.

    juz to sprawdzam i probuje wkleic do swojego pliku

    Dodano po 24 [minuty]:

    *adamas_nt

    zapomniales dodac drukowanie :)
    ActiveSheet.PrintOut Copies:=2

    Dodano po 14 [minuty]:

    ok

    mam nastepne zapytania:

    1. jak zrobic, zeby domyslnym katalogiem zapisu pliku byl ten, z ktorego jest otwarty protokol_wzor ??

    2. zapewne banalne dla wiekszosci - jak zrobic, aby przycisk byl zablokowany taki w zalaczniku od kolegi adamas_nt - czyli mozna tylko kliknac, a prawy przycisk myszy na przycisku nic nie robi ??

    0
  • #18 26 Lut 2011 22:06
    kerim36
    Poziom 10  

    Dzieki za wszystko panowie - efekt zamierzony zostal osiagniety.

    Teraz bede pracowal nad zestawieniem, ktore ma byc generowane na podstawie zapisanych protokolow i pozwole sobie ponownie prosic o wsparcie.

    0
  • #19 03 Mar 2011 19:04
    kerim36
    Poziom 10  

    Witam

    Kontynuując temat rozpoczęty w wątku:
    https://www.elektroda.pl/rtvforum/topic1924114.html

    mam dalsze zapytania.

    Plik (wzór, który został stworzony z Waszą pomocą w wątku powyżej)), który jest uzupełniany to: ZLECENIE.xls

    Na jego podstawie zostały już wygenerowane 3 pliki:
    Zlecenie001-2011.xls
    Zlecenie002-2011.xls
    Zlecenie003-2011.xls
    Domyślna ścieżka dla tych plików to: c:\agata\dostawa\marzec1\


    Z plików ZlecenieXXX-2011.xls ma być generowane zestawienie: zestawienie szef.xls

    Problemy są następujące:
    1. Potrzebuję, aby w wierszach od A5 do E15 (odpowiednio z pierwszego pliku), od A16 do E26 (z drugiego pliku), itd były wczytywane konkretne dane po kolei z wszystkich plików ZlecenieXXX-2011.xls jakie są w całym konkretnym katalogu.

    2. Jak zrobić kopiowanie numeracji zlecenia (przy otwieraniu zlecenia jest on brany z licznika, który jest w osobnym pliku txt). Na myśl przychodzi mi opcja zczytania tego numeru z nazwy pliku - czy macie inne pomysły??

    Z góry dzięki za ponowną pomoc i wspracie
    Mirek
    Połączyłem oba tematy TONI_2003.

    0
  • #20 04 Mar 2011 17:22
    adamas_nt
    Moderator Programowanie

    Po pierwsze: Proponuję kilka zmian w pliku zlecenie. Dołożyłem warunek dla pobierania Nr zlecenia. Moim zdaniem zapisane pliki powinny mieć przypisany Nr, a nie pobierać z pliku. Podobnie ma się sprawa z przyciskiem. Przetestuj i zaopiniuj.

    Po drugie: Czy rozważałeś zamiast "zapisz jako" eksport samego arkusza bez makr?

    Reszty jeszcze nie analizowałem. Przysiądę wieczorkiem...

    0
  • #21 07 Mar 2011 12:17
    kerim36
    Poziom 10  

    *adamas_nt

    rozumiem, ze ten warunek kryje sie w ponizszej linijce ??:
    ActiveSheet.Shapes("Button 28").Delete
    oraz
    If ThisWorkbook.Name = "ZLECENIE.xls" Then


    dzieki za poswiecony czas


    w sumie teraz zlecenie mam takie jak chce (nawet edytowac je mozna bez problemu), jednak nadal nie wiem jak zrobic aby do zestawienia szef w wierszach od A5 do E15 (odpowiednio z pierwszego pliku), od A16 do E26 (z drugiego pliku), itd były wczytywane konkretne dane po kolei z wszystkich plików ZlecenieXXX-2011.xls jakie są w całym konkretnym katalogu.

    0
  • #22 07 Mar 2011 14:16
    Aldrin
    Poziom 22  

    kerim36 napisał:
    jednak nadal nie wiem jak zrobic aby do zestawienia szef... były wczytywane konkretne dane po kolei z wszystkich plików ZlecenieXXX-2011.xls jakie są w całym konkretnym katalogu.

    Nie wiadomo jak zrobić, ale co? Kopiowanie danych (A) czy odczyt wszystkich plików (B) w celu pobrania danych do arkusza szef?

    A.
    Jeśli dane występują w takim samym układzie, to wygodnie jest zaznaczać całe zakresy w jednym arkuszu i wstawiać do drugiego. Warto wtedy rozważyć opcję "wklej tylko wartości". Jeśli dane nie występują w identycznym układzie, to można je wpisać do tablic/y i stamtąd przepisać do arkusza szef.
    Jest jeszcze wariant, że można na sztywno wstawić odwołania do innych arkuszy źródłowych, ale takie rozwiązanie nie jest elastyczne i jeszcze tych arkuszy nie ma ;)

    B.
    Wszystkie arkusze z danego katalogu można pobrać np. przez Dir w pętli.

    0
  • #23 07 Mar 2011 16:31
    kerim36
    Poziom 10  

    *Aldrin

    ad a.Dane, ktore maja byc w zestawieniu sa plikach ze zleceniami dokladnie w tych samych miejscach w kazdym pliku

    ad b. moge prosic o przyklad. na jednym pliku wiem jak, ale jak zrobic zeby dane z kazdego pliku rozpoczynaly sie co 11 wierszy??

    0
  • #24 07 Mar 2011 17:55
    Aldrin
    Poziom 22  

    kerim36 napisał:
    na jednym pliku wiem jak, ale jak zrobic zeby dane z kazdego pliku rozpoczynaly sie co 15 wierszy??

    Proponuję tak:
    1. zrób licznik, np. i=5
    2. przed skopiowaniem zakresu wartości ustaw w pliku szef pozycję na komórce "początkowej" dla zakresu, np. Range("A" & i).Select
    3. po wstawieniu wartości zwiększ licznik i=i+11
    4. i tak w kółko punkty 2 -> 3 -> 4 dla każdego pliku.

    Mam wrażenie, że przesunięcie jest co 11 wierszy (A5 -> A16, itd.)

    0
  • #25 07 Mar 2011 20:45
    kerim36
    Poziom 10  

    *Aldrin

    jezeli dobrze Cie rozumiem to dla kazdej kopiowanej komorki z pliku zleceniexxx mam zastosowac petle, ktora bedzie przenosic/kopiowac wartosci ze zleceniexxx do zestawienie_szef ??

    Dodano po 14 [minuty]:

    wstepnie widze, ze ten skrypt bedzie mi pomocny :)

    Code:
    Sub OperacjeHurtowe()
    
    Dim wbkSkoroszyt As Workbook
    Dim i As Long, szukWiersz As Long, wiersz As Long
    Dim sciezka As String
    ' okreslam sciezke szukania jako podkatalg Dane katalogu gdzie znajduje sie skoroszyt Zbiorczy
    sciezka = ThisWorkbook.Path & "\Dane"
    wiersz = 1 ' zmienna okreslajaca biezacy wiersz w arkuszu gdzie zbieram dane

      With Application.FileSearch   ' zaczynam prace z obiektem FileSearch
        .NewSearch                  ' nowe szukanie
        .LookIn = sciezka           ' sciezka poszukiwan
        .SearchSubFolders = False   ' szukanie w podfolderach - nie
        .Filename = "*.xls"         ' nazwa plików
        .FileType = msoFileTypeExcelWorkbooks ' typ pliku - Excel
       
        ' jezeli liczba znalezionych (i posortowanych przy okazji) jest wieksza od zero
        If .Execute(msoSortByFileName) > 0 Then
            ' robie petle od 1 do liczby znalezionych plików
            For i = 1 To .FoundFiles.Count
                ' otwieram 1 odnaleziony skoroszyt (a w nastepnych obiegach petli  2,3 az do ilosci znalezionych)
                Set wbkSkoroszyt = Workbooks.Open(.FoundFiles(i))
                'aktywuje ten skoroszyt
                wbkSkoroszyt.Activate
                With wbkSkoroszyt.Worksheets(2) ' w pierwszym arkuszu tego skoroszytu
                'szukam ostatniego niepustego wiersza w kolumnach A:G (do poprawienia wedlug potrzeb)
                'mozna tez zamiast Range("E18:AD18") wpisac Cells wtedy bedzie szukal ostatniej niepustej we wszystkich kolumnach
                szukWiersz = .Range("E18:AD113").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
                'kopiuje zakres od drugiego wiersza do ostatniego zapisanego do kolumny G (do poprawienia wedlug potrzeb)
                .Range("E18:AD" & szukWiersz).Copy Destination:=Workbooks("Zbiorczy.xls").Worksheets(1).Range("A" & wiersz)
                'zwiekszam wiersz o ilosc wkopiowanych wierszy - 1 naglówek
                wiersz = wiersz + szukWiersz - 17
                End With ' tyle operacji na tym arkuszu
                wbkSkoroszyt.Close False 'zamykam odnaleziony skoroszyt bez zapisywania zmian
            Next i ' idz do nastepnego skoroszytu
        Else ' jezeli nie znalazlem plików w podanej lokalizacji
            MsgBox "Nie znaleziono zadnych plików" ' daje komunikat
        End If ' koniec mozliwosci odnoscie znajdowania
      End With 'koncze prace w obiektem FileSearch

    End Sub

    0
  • #26 07 Mar 2011 21:00
    Aldrin
    Poziom 22  

    kerim36 napisał:
    dla kazdej kopiowanej komorki z pliku zleceniexxx mam zastosowac petle, ktora bedzie przenosic/kopiowac wartosci ze zleceniexxx do zestawienie_szef ??

    Można i tak.
    Skoro jednak ustaliliśmy, że kształt zakresów jest taki sam, to znacznie szybciej będzie zaznaczyć CAŁY zakres w zleceniu -> skopiować go -> wskazać u szefa komórkę "startową" (lewy górny róg dla zakresu) -> wkleić wartości.

    Mniej więcej tak:
    plik zlecenie (zaznacz zakres i kopiuj)
    - Range("C11:I15").Select 'ustaw swój zakres
    - Selection.Copy

    plik szefa (wklej wartości)
    - Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

    (minusy usuń)
    Najszybciej można to zrobić rejestrując makro. Później z makra wytnij tylko to co najważniejsze i wstaw do swojego makra. Po skopiowaniu danych nie zamykaj pliku zlecenia, bo nie będzie co wklejać.

    Pętlę lepiej zastosuj do wczytywania plików kolejnych zleceń.

    0
  • #27 07 Mar 2011 21:10
    kerim36
    Poziom 10  

    Aldrin napisał:

    Można i tak.
    Skoro jednak ustaliliśmy, że kształt zakresów jest taki sam, to znacznie szybciej będzie zaznaczyć CAŁY zakres w zleceniu -> skopiować go -> wskazać u szefa komórkę "startową" (lewy górny róg dla zakresu) -> wkleić wartości.



    racja. przeciez to oczywiste :)

    co do nagrania makra ok, ale jak to powiazac z kwestia otwierania kazdego pliku zleceniexxx z danego folderu?? (ich ilosc bedzie rozna w zaleznosci od miesiaca)

    0
  • #28 07 Mar 2011 21:38
    Aldrin
    Poziom 22  

    kerim36 napisał:
    ...z kwestia otwierania kazdego pliku zleceniexxx z danego folderu?? (ich ilosc bedzie rozna w zaleznosci od miesiaca)

    Za to wszystkie będą w jednym folderze, tak?
    Pętla wczytuje WSZYSTKIE pliki spełniające kryteria, bez względu na miesiąc w którym będzie uruchomiona (ilość plików), więc wszystko powinno być OK.
    Sprawdź tylko czy aktualnie analizowany plik nie nazywa się szef.xls, żeby go nie wczytać (jeśli jest w tym samym katalogu co zlecenia).

    0
  • #29 07 Mar 2011 21:52
    kerim36
    Poziom 10  

    tak, wszystkie pliki beda w jednym folderze
    analizowane pliki beda sie zawsze nazywac zlecenieXXX.xls

    a plik z ktorym bedzie zestawienie bedzie sie nazywac zestawienie szef.xls - wiec zupelnie rozne nazwy

    0
  • #30 07 Mar 2011 23:57
    Aldrin
    Poziom 22  

    Czyli... wszystko jasne. Pochwal sie wynikami ;)

    0