Copy & Paste from clipboard

[avisam]

Hello,

I am copying a range from excel (multiple rows)
and paste it directly to access table using:

DoCmd.RunCommand acCmdPasteAppend

Sometimes it does not work and I need to use paste special as text.

Is there any way to paste special as text using VBA ?

Thank you

 

[vbaInet]

To keep things very simple, follow these steps:

1. Copy
2. Move to a new sheet or unformatted area in the current sheet - my preference would be a new sheet
3. Paste Special in Excel: Cells().PasteSpecial xlPasteValues
4. Copy
5. PasteAppend in Access
6. Delete sheet in Excel (if you created one) or clear the area where you pasted special (if you didn't create a sheet)

Other methods are:
* Loop through the cells (or array) and for each cell add to the recordset of the table in Access
* MS Windows Common Controls comes with a Clipboard object so you can investigate that
* API calls to the clipboard - there are examples on the net related to this

 

[avisam]

Thank you.

I saw that I tried to use PasteAsValues before and replaced it with PasteAll because I have some columns with numeric data that should be recognized as text (like: "004433"). In PasteAsValues excel recognize these columns as numbers.

I will try your additional methods.

Thank you for your support.

 

[vbaInet]

I saw that I tried to use PasteAsValues before and replaced it with PasteAll because I have some columns with numeric data that should be recognized as text (like: "004433"). In PasteAsValues excel recognize these columns as numbers.

There are several ways of getting around that:

* Change the format of the column that you're copying from to Text:

.Columns("A").EntireColumn.NumberFormat = "@"

Or

* Change the format of the destination column to Text - same code as above

Or

* Before copying the column, concatenate the empty string to it and it will become text:

.Range("B1:B100").Formula = "=A1 & [COLOR="Blue"]""""[/COLOR]"

... don't worry, the A1 formula will propagate itself along B1 to B100 and the reference will automatically change from A1 to A2 and so on.

Or

* Copy the values as they are and handle the formatting in Access using a query