Trouble including paste special in macro

Hello,
Wondering if there is anyway to include the paste formats and values into the below macro? Every time I try I get a Compile error, expected end of statement.

Thanks

Include This

Please Login or Register  to view this content.

Into this

Please Login or Register  to view this content.

This is the entire code it is placed in a function module, I got it from this site: http://www.rondebruin.nl/win/s3/win024.htm
It works fine I would just like to be able to do the paste and keep all the cell formatting from the file I copied from. Thanks

Please Login or Register  to view this content.

yes, you are correct. I guess I did cross post. I didn't know the two boards were ran and monitored by the same folks. Sorry

They are not run by the same folks, but many of us have accounts on both. Both boards have rules against cross-posting.

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

Your question is still not clear. The code you posted does not have a copy anywhere. So what is it you want to paste, and where do you want to paste it?

Thanks for sticking with me on this one.
The way I understand it the option explicit code I have above I have to put in a Function module.
The code that I have below I put into a regular module, which is copying/pulling the range A1:D10 from the spreadsheet called ToBeCopied.xlsx and importing/pasting or some kind of way bringing it into my current workbook and entering it in starting in cell A1.

Hope that helps.

Please Login or Register  to view this content.

In reading through the code you posted in more detail, I see you are using an ADO connection to treat an Excel file as a database and retrieve data. This uses a SQL query. This is a fancy way to get data from a workbook without opening it. However, a SQL query returns data only, not formatting. So you cannot retrieve this data keeping the original formatting.

I only know two ways to copy data from a closed workbook (this is one, the other uses an Excel4 macro call) and neither one retrieves formatting.

Is there a particular reason that you don't want to open this workbook? If you can tolerate that, the code becomes much easier.

Just to make a point to the poster...

a SQL query returns data only, not formatting

That was raised yesterday: http://www.ozgrid.com/forum/showthre...820#post768820

That is exactly what I meant by "...some of those replies will be similar, if not identical. If that happens then someone has wasted their time".

If you had the courtesy to follow forum etiquette you would have saved time, both for yourself and others.