hexagon logo
Register Sign in
  • Replies 4 replies
  • Subscribers 31 subscribers
  • Views 59 views
  • Users 0 members are here
Options
Related

VB PCDMIS & Excel with Copy-PasteSpecial and Skip Blanks

bfire85
Anyone have any luck with VB and getting the SkipBlanks:=True to work when copying one set of cells of a spreadsheet to another spreadsheet?

Basicially what I am doing is having PC-DMIS export data into a CSV using the File I/O commands. (I can customize it better this way). Then I run a script to save that CSV as an XLSX. Then it opens that new XLSX, copies a set of cells, then pastes that into a dedicated spot on a master Excel spreadsheet.

I can get the numbers to paste exactly where I need them to go, however this doesn't seem to want to skip the blanks. The reason why I need skip blanks is because there are formulas on the master sheet that I need to skip over. 


row = 38
column = 42

Pasterange = "C" & row & ":" & "AO" & column

xlSheet3.Range(Pasterange).Select 

xlSheet3.Range(Pasterange).PasteSpecial SkipBlanks:=True, Paste:=xlPasteValues


Do I need to have a different method of assigning the skip blanks?
Parents
  • If it were me...I'd read directly from the csv file by treating it as a plain text file. Grab the data I want, format it as needed, and paste it in to the master file. You'd need to "Text to Columns" so the data is formatted correctly. But once you've done it manually on the master sheet, it will automatically occur on subsequent paste operations.

    Example of "automatic" text to columns:
    Manually copy the data from the csv, open a spreadsheet and paste in to A1. Then perform text to columns on A:A. Delete the data, save and close the sheet. Now, when you programmatically open the sheet and paste data in to A1, excel will automatically perform text to columns.
Reply
  • If it were me...I'd read directly from the csv file by treating it as a plain text file. Grab the data I want, format it as needed, and paste it in to the master file. You'd need to "Text to Columns" so the data is formatted correctly. But once you've done it manually on the master sheet, it will automatically occur on subsequent paste operations.

    Example of "automatic" text to columns:
    Manually copy the data from the csv, open a spreadsheet and paste in to A1. Then perform text to columns on A:A. Delete the data, save and close the sheet. Now, when you programmatically open the sheet and paste data in to A1, excel will automatically perform text to columns.
Children
No Data
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement