+ Reply to Thread
Results 1 to 10 of 10

Macro Optimization

Hybrid View

  1. #1
    Registered User
    Join Date
    04-12-2007
    Posts
    5

    Macro Optimization

    i have a macro that loads 250 txt files (inside a loop) and do some things with them.
    im using this code to open the file, is there any way to open all the files in the same workbook (now i see an excel window appearing and dissapearing 250 times in taskbar). I think it can speed up a little my macro.
            Workbooks.OpenText Filename:=NombreArchivo _
                , Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
                , ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:= _
                False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1) _
                , Array(3, 4), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), _
                Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array( _
                16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1)), _
                DecimalSeparator:=".", ThousandsSeparator:=" ", TrailingMinusNumbers:=True
    I'll better explain what I do, i open that workbook from a TXT select ALL and paste it in one of the 12 sheets of my workbook (starting from A2 because i have the titles in the row 1). Is there any way to load the files in one step the there or the copy paste i do is necesary?

    thanks and sorry for my english

    Alex

  2. #2
    Registered User
    Join Date
    03-08-2007
    Location
    Brisbane, Australia
    Posts
    57
    hi Alex

    Try using this to stop the screen from flickering as your code runs:
    Application.ScreenUpdating = False
    James

  3. #3
    Registered User
    Join Date
    04-12-2007
    Posts
    5
    im using that and doing the calculations at the end of the macro, i only want not to open 1 excel instance for each file
    thanks

  4. #4
    Registered User
    Join Date
    03-08-2007
    Location
    Brisbane, Australia
    Posts
    57
    Ok

    I assume then that the problem is that you end up with 250 files open at the end, then, rather than all files being appended to one worksheet.

    Looking at help on OpenText it seems to create a new workbook by default:
    "Loads and parses a text file as a new workbook with a single sheet"
    The Open method of the Workbooks collection has the same effect. I feel that you'll need to program a combining routine of your own.

    There are some other optons worth investigating:

    You could try importing the files using Query Table object, rather than opening them as files.

    Personally I would always try to get external files built as CSVs and appended prior to running Excel.

    Thought: do you know how to run a DOS Batch file to combine the files before opening them (as one text file)?

    James

  5. #5
    Registered User
    Join Date
    04-12-2007
    Posts
    5
    Thought: do you know how to run a DOS Batch file to combine the files before opening them (as one text file)?
    I know how to do that, but now each file have like 10k lines...there are 250 files... 2.500.000 lines its a little too much.
    I end up with only one excel open (the one with the macro) but a new one open to copy the content to the first one.
    I want to load the file to a sheet in my first excel starting from A2, if that cant be done, then I want to keep the other excel open and work loading and clearing 1 sheet.
    If the first thing can be done it will improve the performance, if not, i think the second will do something too.
    sorry about my english
    thanks again

  6. #6
    Registered User
    Join Date
    03-08-2007
    Location
    Brisbane, Australia
    Posts
    57
    so let me get this right . . .

    you want the macro (which is stored in Workbook1) to:

    0. Set the TOP as cell A2
    1. open the TextFile1 as Workbook2
    2. select all data in Workbook2
    3. copy all data to clipboard
    4. activate Workbook1
    5 select Workbook1 at TOP
    6. paste the data
    a. if the paste doesn't fit on the sheet, move to A2 on the next sheet
    b. do step 6 again]
    c. find the last row of the pasted data]
    d. set TOP as the next row down]
    7. activate Workbook2
    8. close Workbook2
    9. open textfile 2 as Workbook2
    10. repeat steps 1-8
    11. open textfile 3 as Workbook2
    12. repeat steps 1-8
    ..... for each of 250 text files


    Is this how it should work?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1