VBA to copy specific cells from all files in a folder

**Mehul Jaganiya** · 11-02-2014, 07:05 PM

Hi guys,

I am trying to copy values from specific cells in all the files stored in a folder and paste it onto a new file. I found a code which does almost what I am after. The only problem here is that it can not access the file names with an apostrophe like "Simon's Boilers", "Baker's extra", "Sauer's bakehouse" etc.

Is there a way to fix this thing? Other than that it is all good.

Please Login or Register  to view this content.

**Trebor76** · 11-02-2014, 07:33 PM

Hi Mehul,

The problem is that in your code (by design) a single apostrophe denotes a the start or end of a field. To get around this you to write code to replace any single apostrophe in the fn filename variable with two apostrophes, which though I can't test without being on your server, I would use something like this...

Please Login or Register  to view this content.

...just below this line:

Please Login or Register  to view this content.

HTH

Robert

**Mehul Jaganiya** · 11-02-2014, 07:50 PM

Thanks a lot Trebor76. I just had to change the code a bit as below and it worked out perfectly.

Please Login or Register  to view this content.

**Mehul Jaganiya** · 11-02-2014, 07:52 PM

I have one more query if you could please help me.

As you see the code is operating in the worksheet named "Hours". Is it possible to program the code to look in Sheet2 if "Hours" worksheet is not present?

Thanks once again.

**Trebor76** · 11-02-2014, 09:06 PM

Is it possible to program the code to look in Sheet2 if "Hours" worksheet is not present?

Though it's much easier to do this while the file is open, try these lines of code...

Please Login or Register  to view this content.

...immediately under this existing line:

Please Login or Register  to view this content.

Regards,

Robert

**Mehul Jaganiya** · 11-02-2014, 09:24 PM

Thanks Robert. It still asks me to select the sheet every time it can't find Hours worksheet.

**Trebor76** · 11-02-2014, 09:29 PM

Without being on your server and testing it's very hard to recommend a solution, but see how this goes:

Please Login or Register  to view this content.

Robert

**Mehul Jaganiya** · 11-02-2014, 09:51 PM

I absolutely agree it is difficult to recommend a solution this way. It still continues to do the same thing.

Not sure if this will help, here is a screenshot of the point where excel asks me to select a sheet as it couldn't find Hours sheet. Hope it helps.

Untitled.jpg

**Trebor76** · 11-02-2014, 10:48 PM

Not sure if this will help, here is a screenshot of the point where excel asks me to select a sheet as it couldn't find Hours sheet. Hope it helps.

No it doesn't as the attachment doesn't open

**Mehul Jaganiya** · 11-02-2014, 11:43 PM

I'm sorry for that.
I have attached it once again or you can use this link to access it.

http://postimg.org/image/7jbyuzx6v/

Untitled.jpg

**Trebor76** · 11-03-2014, 12:06 AM

OK, that really doesn't help either but try this where I use an ADO connection to the workbook to determine if the sheet exists or not:

Please Login or Register  to view this content.

Again this is untested so it may need some tweaking on your side. Also note the comment regarding making a reference to the Microsoft ActiveX Data Objects library which is required before you try and run the macro.

Regards,

Robert

**Mehul Jaganiya** · 11-05-2014, 04:50 PM

Thanks for your help Robert. Unfortunately this did not work either. So to make it less complicated I manually sorted out the files from the folder which did not have HOURS spreadsheet on it.

**Mehul Jaganiya** · 11-05-2014, 04:55 PM

I have just one last issue with this code. The code always gives the output in the first sheet of the workbook. For example, even if I have the code in Sheet3 and run it there the output goes to Sheet1. How can I fix that?
Here is code that I am using currently.

Please Login or Register  to view this content.

VBA to copy specific cells from all files in a folder

LinkBack

Thread Tools

Rate This Thread

Display

VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Re: VBA to copy specific cells from all files in a folder

Thread Information

Users Browsing this Thread

Similar Threads

Unzip all files in a specific folder and move CSV files to a different specificed folder

[SOLVED] Need to copy values from all files in folder from specific worksheet

[SOLVED] VBA Code open files in folder, copy text to workbook-Next time folder opened copy only new

[SOLVED] Copy 10 rows from specific files in a folder

how can I specific a folder with wildcard criteria and excel will import all the correct files in that folder?

Bookmarks

Bookmarks

Posting Permissions