+ Reply to Thread
Results 1 to 4 of 4

Compile code for clear cells, open a workbook with WindowsExplorer, copy&paste, close it

  1. #1
    Registered User
    Join Date
    05-30-2019
    Location
    Köln
    MS-Off Ver
    W10Pro
    Posts
    35

    Compile code for clear cells, open a workbook with WindowsExplorer, copy&paste, close it

    Hi all,

    please help me with the compile&correction of the following code.
    On short:
    (Done)1 - I am in the AR sheet (where I work, "FORM" workbook);
    (Done)2 - press the "Update" command button;
    (Done)3 - delete all records from the AR sheet (active sheet);
    (Done)4 - WindowsExplorer opens to choose the .xls document;
    (Pause)5 - the selected document opens;
    (Done)6 - copy column A from the only sheet existing in this document (so the sheet is pre-activated);
    (Pause)7 - the source document is closed;
    (Pause)8- the AR sheet is reactivated (workbook "FORM" - I think, I hope) where it pastes only values in column A;
    (Done)9- find and replace non-compliant characters;
    (Done)10 - sort and filter the information according to the criteria;


    Step 3 - they need 2 times because they are filtered
    Please Login or Register  to view this content.
    Step 4 - open WindowsExplorer for selecting the file( I don't know how to open the selected file, or rather in Worbook.Open I don't know how to integrate WindowsExplorer to select open file)
    Please Login or Register  to view this content.
    Step 6/8 - copy&paste values from/in Column A
    Please Login or Register  to view this content.
    Step 9&10 - like a charm
    Please Login or Register  to view this content.
    I appreciate any attempt and thank you in advance for your effort!!!

    and a question of my level, noob:
    do I write the code in the sheet code or in a separate module that I call with the Update command button?

  2. #2
    Registered User
    Join Date
    05-30-2019
    Location
    Köln
    MS-Off Ver
    W10Pro
    Posts
    35

    Re: Compile code for clear cells, open a workbook with WindowsExplorer, copy&paste, close

    Until now, with both workbooks open, code is working ok, without freezing.(must WindowsExplorer introduced)
    Please Login or Register  to view this content.
    but, if i try to introduce(before to filter result) 3 columns and to do CONCATENATE in first 2 columns(i need for first 5000 rows), is freezing document for a long time(3-4 minutes), even if is doing what i need.
    Please Login or Register  to view this content.
    that's all I've managed to compile so far!!

  3. #3
    Registered User
    Join Date
    05-30-2019
    Location
    Köln
    MS-Off Ver
    W10Pro
    Posts
    35

    Re: Compile code for clear cells, open a workbook with WindowsExplorer, copy&paste, close

    i try and

    if i tested separat:(I want to enter (2) in (3) before filtering data)

    (1)delete 3 columns = 6 seconds(with delete concatenate for 5000 rows)

    (2)insert 3 columns = 10 seconds(with concatenate first 2 columns untill row 5000, with data unfiltered)

    (3)delete contents & import data & replace wrong characters & filter data = 7 second(inclusive ok in pop up for replace existing data)

    so in maximum 30 seconds it should be ready, instead combined all 3 blocks the document for a good period.

  4. #4
    Registered User
    Join Date
    05-30-2019
    Location
    Köln
    MS-Off Ver
    W10Pro
    Posts
    35

    Re: Compile code for clear cells, open a workbook with WindowsExplorer, copy&paste, close

    that is, I hope it's ok, unfortunately I destroyed the .csv file, it opens directly separately on columns, so I can no longer use that document to import data and continue the code (to see if really works until the end, and how long it dauer)


    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copy from first workbook, close open new workbook paste close
    By johncarr1952 in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 08-04-2021, 08:50 AM
  2. Replies: 2
    Last Post: 03-18-2017, 02:05 AM
  3. Open workbook copy&paste, save and close Repeat
    By JPSIMMON in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-06-2016, 12:29 PM
  4. Macro VBA to copy and paste cells from one open workbook to another open workbook
    By bloomingcarrot in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-23-2014, 07:55 PM
  5. Replies: 0
    Last Post: 07-12-2013, 05:29 PM
  6. Replies: 13
    Last Post: 11-29-2012, 08:25 PM
  7. Replies: 2
    Last Post: 09-11-2012, 09:42 AM

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