[VBA/Excel2000] Openen, bewerken en opslaan van veel files*

[Pancake]

Om te beginnen heb ik geen enkel verstand van macro's. Zo af en toe neem ik er wel eens eentje op in Excel, maar dat is het dan wel. Nu moet ik op mijn werk 220 txt-files met elk 65.000 regels in Excel 2000 importeren, daar een bewerking op uitvoeren en weer exporteren als een txt-file. Gezien het grote aantal files en de omvang van de bestanden wil ik dit in een macro stoppen en liefst zodanig dat ik alleen maar 220 keer gevraagd wordt om een file te selecteren die geopend moet worden en 220 keer bij het opslaan een naam in moet vullen.

D.m.v. het opnemen van een macro heb ik deze hele handeling voor één file gereed, maar ik stuit op het probleem dat de filenamen bij het openen en het saven zijn "vast" en hier moet eigenlijk het dialoogvenster komen om een bestand te openen (danwel te sluiten waarin ik de namen op kan geven). Hoe doe ik dat? Oh ja, de namen van de te saven bestanden mogen evt. ook automatisch gegenereerd worden (!1.txt, !2.txt, !3.txt, enz), maar met het zelf opgeven van een bestand ben ik al ruimschoots geholpen.

Ik heb de help van Excel geprobeerd, maar als je zelfs de structuur van vba niet doorhebt heeft dat weinig zin.

Ter info de stappen die ik doorloop:

01) Open de excelfile "1.xls";
02) Open de txt-file in excel;
03) Selecteer en kopieer de kolommen A, B en C;
04) Plak deze drie kolommen in de kolommen A, B en C op "blad1" van de spreadsheet "1.xls";
05) Ga naar "blad2"
06) Selecteer en kopieer de kolommen A en B;
07) Plak met "plakken speciaal - waarden" deze twee kolommen in de kolommen A en B op "blad3";
08) Ga naar blad 2"
09) Selecteer en kopieer kolom E;
10) Plak met "plakken speciaal - waarden" deze kolom in kolom C op "blad3";
11) Zoek de eerste waarde "0" op en wis vanaf dat punt alles;
12) Selecteer de cellen Ax en Bx van de laatste gevulde regel van de spreadsheet;
13) Kopieer deze celwaardes in regel lager;
14) Zet in de C-kolom achter deze waarde iets van "0.0000D+5"
15) Ga één regel lager en zet in A het getal 999;
16) Ga nog eens één regel lager en zet in A het getal 999;
17) Sla "blad3" op als een "tekstbestand - tab is scheidingsteken";
18) Sluit de excelbestanden die in de stappen "01" en "02" zijn geopend;

Omdat bij het opslaan van het bestand in stap "17" de naam van het tabblad verandert in de naam
van het opgeslagen bestand, moet ik de eigenlijke macro aanroepen vanuit een lege excelfile en
moet bij ieder te verwerken bestand het originele rekenblad "1.xls" weer geopend worden.

Ter info volgt hieronder de macro zoals ik deze heb opgenomen:

code:

Sub Inlezen()

' Inlezen Macro

    Workbooks.Open Filename:="D:\1.xls"
    Workbooks.OpenText Filename:="D:\test1.txt", Origin:=xlWindows, StartRow _
        :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True _
        , Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
        Array(3, 1))
    Columns("A:B").Select
    Selection.Copy
    Windows("1.xls").Activate
    Sheets("Blad1").Select
    Columns("A:B").Select
    ActiveSheet.Paste
    Windows("test1.txt").Activate
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("1.xls").Activate
    Columns("C:C").Select
    ActiveSheet.Paste
    Sheets("Blad2").Select
    Columns("A:B").Select
    Range("A4").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Blad3").Select
    Columns("A:B").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Windows("1.xls").Activate
    Sheets("Blad2").Select
    Columns("C:C").Select
    Range("C4").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Blad3").Select
    Columns("C:C").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("A1").Select
    Cells.FindNext(After:=ActiveCell).Activate
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveCell.Offset(-1, 0).Range("A1:B1").Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 2).Range("A1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "0.00000D+5"
    ActiveCell.Offset(1, -2).Range("A1").Select
    ActiveCell.FormulaR1C1 = "999"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveCell.FormulaR1C1 = "999"
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveWorkbook.SaveAs Filename:="D:\!1.txt", FileFormat:=xlText, _
        CreateBackup:=False
    Windows("test1.txt").Activate
    ActiveWorkbook.Close
    Application.WindowState = xlMinimized
    ActiveWorkbook.Close
End Sub

Alvast bedankt voor jullie moeite

[ Voor 8% gewijzigd door Pancake op 03-07-2005 14:22 ]

[zwippie]

Pancake schreef op zondag 03 juli 2005 @ 14:21:
...Gezien het grote aantal files en de omvang van de bestanden wil ik dit in een macro stoppen en liefst zodanig dat ik alleen maar 220 keer gevraagd wordt om een file te selecteren die geopend moet worden en 220 keer bij het opslaan een naam in moet vullen....

Als je van te voren al weet om welke 220 bestanden het gaat, kun je ook een lijst (tekstbestand) maken met alle bestandsnamen, dat scheelt je later een hoop geklik.

[NMe]

VBA en alle andere dingen die met office-producten te maken hebben horen tegenwoordig in Officesuites en -software.

PW>>OFF

[Pancake]

zwippie schreef op zondag 03 juli 2005 @ 15:24:
[...]

Als je van te voren al weet om welke 220 bestanden het gaat, kun je ook een lijst (tekstbestand) maken met alle bestandsnamen, dat scheelt je later een hoop geklik.

Ik weet inderdaad om welke bestanden het gaat, dus een lijstje zou ik zo kunnen maken. Ik neem aan dat je de macro dan "voedt" door iedere keer de volgende regel van dat lijstje te pakken? Alleen heb ik geen flauw idee hoe ik dat moet doen.

Als alternatief kan ik d.m.v. zoeken en vervangen dat lijstje ook omvormen in een supergrote macro (dus 220 keer dezelfde handelingen achter elkaar), maar mogelijk loop ik dan tegen een max. omvang van een macro aan (zou ik naar moeten zoeken)

[onkl]

Wat ook kan is:
Zet alle 200 bestanden in een aparte folder en wijs een andere folder aan voor je output. De bestandsnamen kunnen dan gelijk blijven.
Het sleutelwoord is dan "filesearch". Lompe copy paste van de excel VBA help:
Using the FileSearch Object
Use the FileSearch property to return the FileSearch object. The following example searches for files and displays the number of files found and the name of each file.

With Application.FileSearch
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

Use the NewSearch method to reset the search criteria to the default settings. All property values are retained after each search is run, and by using the NewSearch method you can selectively set properties for the next file search without manually resetting previous property values. The following example resets the search criteria to the default settings before beginning a new search.

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents"
.SearchSubFolders = True
.FileName = "Run"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
End With
(Einde paste)
In het eerste voorbeeld kan je bijvoorbeeld je eigen code plakken tussen de for en de next regel.
De methode om een werkboek op te slaan heet gewoon SaveAs.
De VBA help zal je wel een eind op weg helpen.

[Pancake]

Ik ben vanmorgen begonnen door met zoeken en vervangen een lijst met files om te toveren in een mega-macro's gemaakt waarmee ik de klus geklaard heb. Desondanks toch heel erg bedankt voor jullie inbreng.

Nu mijn baas nog overtuigen van het nut van een VBA cursusje.