Open workbook and paste in first empty cell

**tomant_123** · 01-02-2013, 05:41 AM

I'm trying to put a macro together that after opening a log file, automatically copies the results to first empty cell in another excel file.
The log files are always named "test xxx", with xxx a specific number. The file were the data needs to be copied to is named "2013-0xxx". From this log file I want to copy cell A75 and cell D75, respectively to the first empty cell in column A and E.

For example, there is a log file named "test 621". From this file I want to copy cell A75 and D75 to the first empty cell in column A and E of a file named "2013-0621".

The first problem is to open the correct file. I can open a file named "621", but not "2013-0621". Secondly, I get an error in my code to copy and paste in the first empty cell. Hope someone can help.

Please Login or Register  to view this content.

**OllieB** · 01-02-2013, 05:58 AM

Slightly different approach

Please Login or Register  to view this content.

There should be no problem using "2013-0621" as a name, but then you will need to change the below statement

Please Login or Register  to view this content.

to (assuming the part still starts at position 6 in the name of the source worksheet)

Please Login or Register  to view this content.

Hope this helps

**tomant_123** · 01-02-2013, 06:46 AM

Code works perfect for pasting in the first empty row! Thanks!
However, my problem with the file name still remains. The file "2013-0621" should be automatically detected after selecting file "test 621". My thought was to select the "621" out of the log file name "test 621" and then try something like this to open the "2013-0621" file.

Please Login or Register  to view this content.

But I always get an error message.
Ideally, it should automatically open the .xls file containing "621" in it's file name. There is only one file "621", being "2013-0621". But older files are for example "2012-0500" or "2011-0100". So it would be nice if after selecting a log file "test 500", the data would automaticaly be copied to "2011-0500.xls". I hope this is clear.

**OllieB** · 01-02-2013, 07:21 AM

Hi Tomant,

Good to hear the first part works.

How would the solution know whether it needs to prefix 2013, 2012 or 2011?

in the example provided by you the text "2013-0" should be enclosed in quotes, but I am sure there is a better solution once you answer the question above

Please Login or Register  to view this content.

**tomant_123** · 01-02-2013, 07:37 AM

Hi Olaf,

Thanks for your help! All files are in the same folder, and the numbers in the file name are unique. So there is only one log file containing 621 ("test 621.txt") that needs data to be copied to "2013-0621.xls". For example, if it's possible to simply search for an .xls file containing "621" and open that file. I think that would do the trick. Then the prefix would be undefined.
Another idea is to manually set limits, because all files from "Test 508" to "Test 620" need to be copied to a file with a "2012-0" prefix. From file "test 621" on, the prefix will be "2013-0". But that would ofcourse need an additional line of code every year to update the limits.

**OllieB** · 01-02-2013, 07:55 AM

Ok,

what we can try is the following code

Please Login or Register  to view this content.

**tomant_123** · 01-02-2013, 09:10 AM

Works like a charm! Thanks!

**OllieB** · 01-02-2013, 09:13 AM

Originally Posted by tomant_123

Works like a charm! Thanks!

You are very welcome. Please remember to mark the thread as SOLVED, and to click the star icon if you are happy with my contribution

Open workbook and paste in first empty cell

LinkBack

Thread Tools

Rate This Thread

Display

Open workbook and paste in first empty cell

Re: Open workbook and paste in first empty cell

Re: Open workbook and paste in first empty cell

Re: Open workbook and paste in first empty cell

Re: Open workbook and paste in first empty cell

Re: Open workbook and paste in first empty cell

Re: Open workbook and paste in first empty cell

Re: Open workbook and paste in first empty cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions