Clipboard to Datatable


#1

Hello,

I’m trying to extract a table of data from an application I use at work. The application will produce the data in a temporary instance of excel. I want to copy the data from the temporary instance, and paste it into an actual excel workbook on my harddrive.

I was planning to copy the data from the excel instance (ctrl + c), get clipboard to a DataTable, and then use that datatable to write range to Excel.

However, when I use the “get from clipboard” feature, I get an error if I try to output to a DataTable variable. It appears that I can only copy it to a GenericValue.

Is there a way to either copy the clipboard as a DataTable, or convert the generic value to a DataTable?

Thanks in advance


#2

Hi,

My recommendation is to not use Clipboard in this case. I would instead recommend that you use Excel Scope to attach to the temporary instance or Excel file, then use Read Range store the datatable. With that datatable you can use Write Range to output it to your final Excel file.

If you would however prefer the Clipboard method, you need to use Ctrl+c on the Excel instance and that will place the table in the Clipboard (no datatable used). Then, use Ctrl+v to paste it from the Clipboard to your desired spreadsheet.

It’s usually ideal to avoid using Clipboard, but there are exceptions depending on the source of the data.

Regards.

C


#3

Hi Clayton, thanks for your response. Is there any guidance out there for connecting the Excel Scope to a temporary workbook? I don’t think the software (SAP) actually save the file in a temporary location.

Thanks again


#4

That is true; you need the file location for Excel Scopes. Technically the file downloaded gets saved in the AppData folder like in the Temp folder I believe.

However, what if you just saved the temporary file? You can do this by clicking Save As or using Ctrl+s or TypeInto “[k(alt)]fa”. Then type in the full filepath of where you want to save it and click Save.

After it’s saved, the Excel Scope will attach to it if it’s already open.

Hopefully that helps.

Thanks.


#5

Great thought. Thanks!