Clipboard Paste Question

[GooGoocluster]

Is there a way to paste data into a table using VBA or macros. the data has column headers and currently I am pasteing it into excel, saving it then importing it into a table. I hope you guys can help thanks

 

[MajP]

What is the original data format? Access can import and link several different data formats to include csv, txt, tab seperated data.

You probably can not paste like you can with excel.

 

[kjv1611]

GooGoocluster,
You might can paste using a recordset to loop through a table's fields, but performance-wise, and integrity-wise, it's best to just import/link to the data if at all possible, as MajP suggested.

If it's a text file, you can use [blue]DoCmd.TransferText[/blue]

--

"If to err is human, then I must be some kind of human!" -Me

 

[GooGoocluster]

we capture the data from a AS400 Screen (like DOS) I would just like to that but I dont know the table names so I can make a connection string. How can I do what you said kjv1611?

"You might can paste using a recordset to loop through a table's fields

 

[kjv1611]

Well, if you're using VBA to copy from that screen from Excel, then you should be able to do the same in Access.

Also, in your mainframe database, there should be a way to export to a text file - comma or tab delimited or something. If there's enough data worth even having a table in the first place, then you would be better off doing it that way.

As for a connection to the tables, well, that may not even be possible.

--

"If to err is human, then I must be some kind of human!" -Me

 

[GooGoocluster]

sorry I did not clarify very well. me <-- not very good at explaining things. Right now I am using the copy selection button on the terminal window to copy it into the clipboard then put it into excel. what I was invisioning I could to is take what is in the clipboard and dump it into a datasheet(perferibly not visible) then take that table and extract the data I want from that. I hope this helps explain it a little better.

Thanks for your help so far

 

[kjv1611]

Hmm, well, I think you might could do it this way:

In Access, use VBA code to "grab" the current clipboard data, and store it in a string variable.

Then, in the VBA Code, you can use functions such as Left(), Split(), Right(), etc, whatever works for the specific scenerio, anyway, and split out the string to individual fields/records according to where the text is (looking at line breaks, spacing, etc), then you could use the same VBA code to feed that data into a record set of a predefined table.

So, basically, short-term, easiest thing is to just keep doing what you're doing manually.

But if you have the time, and want to really work on something that could turn out to be quite a challenging and learning experience, you can do it with the VBA code I was talking about.

If you want to try that route, then do it one step at a time. That is, step one: go do some research on how to get the values from the Clipboard. There are lots of sample code snippets, even whole modules that are freely available on the web. I've used at least a couple, myself.

Then, next step, is to determine precisely how to split out that data into individual fields.

The last step will be updating the table with those values, but that will be the easiest by far.

It would definitely be fun if you like learning new things such as some mild programing, but I don't think it's something you'll have working by, say, tomorrow.

--

"If to err is human, then I must be some kind of human!" -Me