Copy contents of VBA csv export to Clipboard

[caj1980]

I have a functioning VBA macro to export the contents of a worksheet to a .csv file. I am curious if anyone knows how to supplement the below script so that the contents of that exported .csv file are copied to the system Clipboard for pasting into a separate program. The current method is to open the exported csv, manually copy contents, then paste to external program. Would be handy to auto-copy to clipboard and reduce the extra processing.
Thanks in advance!


Sub ExportAsCSV()

Dim MyFileName As String
Dim CurrentWB As Workbook, TempWB As Workbook

Set CurrentWB = ActiveWorkbook
ActiveWorkbook.ActiveSheet.UsedRange.Copy

Set TempWB = Application.Workbooks.Add(1)
With TempWB.Sheets(1).Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With

'Dim Change below to "- 4" to become compatible with .xls files
MyFileName = CurrentWB.Path & "" & Left(CurrentWB.Name, Len(CurrentWB.Name) - 5) & ".csv"

Application.DisplayAlerts = False
TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
TempWB.Close SaveChanges:=False
Application.DisplayAlerts = True
End Sub

 

[caj1980]

Another tweak I just realized I need...

Is it possible to tell the script to exclude Column A in the csv export?

Column A is used as the reference for formulas pulling across multiple sheets of the workbook to create the exact format required for the csv, but that reference itself should not be part of the csv.

 

[John_w]

To omit column A in the data exported to the .csv file change the UsedRange.Copy to:

    ActiveWorkbook.ActiveSheet.UsedRange.Offset(, 1).Copy

Making the clipboard contents available to other applications is quite tricky because Excel clears the Clipboard in response to certain user actions, e.g. pressing Esc or Enter, double-clicking a cell, saving the workbook, etc. You could just repeat the ActiveWorkbook.ActiveSheet.UsedRange.Copy before the End Sub to copy the range back to the Clipboard, however if you do something to lose the 'marching ants' then it means Excel has cleared the Clipboard.

Fortunately, Jaafar has written a brilliant piece of code which preserves the Clipboard against user actions - https://www.mrexcel.com/forum/excel...earing-clipboard-post4821079.html#post4821079. As stated in his post, put all his code in the ThisWorkbook module, but change all the LongLong's to LongPtr so that it compiles on 32- and 64-bit Excel.

And change your macro to:

Sub ExportAsCSV()

    Dim MyFileName As String
    Dim CurrentWB As Workbook, TempWB As Workbook
    
    Set CurrentWB = ActiveWorkbook
    ActiveWorkbook.ActiveSheet.UsedRange.Offset(, 1).Copy
    
    Set TempWB = Application.Workbooks.Add(1)
    With TempWB.Sheets(1).Range("A1")
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
    
    'Compatible with any Excel file - .xls .xlsx, etc.
    MyFileName = Left(CurrentWB.FullName, InStrRev(CurrentWB.FullName, ".") - 1) & ".csv"
    
    Application.DisplayAlerts = False
    TempWB.SaveAs Filename:=MyFileName, FileFormat:=xlCSV, CreateBackup:=False, Local:=True
    TempWB.Close SaveChanges:=False
    Application.DisplayAlerts = True
    
    'Copy to clipboard again
    ActiveWorkbook.ActiveSheet.UsedRange.Copy
    
End Sub

Then save, close and reopen the macro workbook.

 

[caj1980]

John_w; thank you!

this is working with just one issue. Now the exported csv files has extra commas at the end of each line. Some basic research sounds like this is from excel finding empty values in expected columns. Can the macro be updated to point at columns B-Q only? I think this will get rid of the extra commas.