hexagon logo
Register Sign in
  • Replies 4 replies
  • Subscribers 31 subscribers
  • Views 60 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?
  • I think what I'll do is calculate those formulas in PC-DMIS and export that data as a work around. I'd still be interested in if anyone knows a way to make the above code work though.
  • Or paste the data to another sheet and have formulas on your main sheet reading the values from the 'data' sheet
  • Hmm never thought about that! I'll have to put that one in the arsenal if needed at a later date. But for what I had to do, it was easier to just assign the formulas to a variable in PC-DMIS and export those out. Thanks for the suggestion!
  • 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.
© 2024 Hexagon AB and/or its subsidiaries. Privacy Policy | Cloud Services Agreement