locked
using vba, how can I save clipboard contents to file? RRS feed

Answers

  • You shouldn't loop through k. Instead, increment k by 1 each time:

    Sub PicClip()
    'this code will copy the range selected.
    'create/modify to iterate through each cell, copy,
    'and then past into an individual file, and save by name.
    'not sure if this is possible, but work through it.
    ' 62 columns by 105 rows. 6510 copy/paste "snapshotted cells."
    'this needs to be a for/each cell, and then iterate through rows.
        Dim wsh As Worksheet
        Dim cht As Chart
        Dim rng As Range
        Dim strFile As Variant
        Dim i As Long
        Dim j As Long
        Dim k As Long
        Set wsh = ActiveSheet
        Set cht = Charts.Add
        Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wsh.Name)
        cht.Parent.Border.LineStyle = 0
        For i = 1 To 105
            For j = 1 To 62
                k = k + 1
                Set rng = wsh.Cells(i, j)
                rng.CopyPicture 1, 2
                cht.ChartArea.Clear
                cht.ChartArea.Width = rng.Width
                cht.ChartArea.Height = rng.Height
                cht.ChartArea.Select
                cht.Paste
                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png"
                cht.EXPORT Filename:=strFile, FilterName:="PNG"
                wsh.Cells(1, 1).Activate
            Next j
        Next i
        wsh.ChartObjects(wsh.ChartObjects.Count).Delete
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SteveDB1 Monday, October 28, 2019 10:27 PM
    Monday, October 28, 2019 9:56 PM
  • Change the line

                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png"

    to

                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & Format(k, "0000") & ".png"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SteveDB1 Monday, October 28, 2019 10:27 PM
    Monday, October 28, 2019 10:21 PM

All replies

  • Hi SteveDB1,

    I hope this article will help you.
    Windows Clipboard & VBA

    Regards,

    Ashidacchi -- http://hokusosha.com

    Friday, October 25, 2019 1:57 AM
  • Hi Ashidacchi (what an interesting name.... it almost seems like a mix between Italian, and middle eastern.).

    That said....

    Thank you for posting this. It's throwing all kinds of errors out the front door. 

    I've cleared out some (formatting, or line-returns), but others remain, and my limited experience is stopping me from taking this any further. 

    Have you ever tried using this in Excel? 

    Monday, October 28, 2019 5:31 PM
  • Try this macro as starting point, you can change it to suit your purpose:

    Sub RangeToImageFile()
        Dim wsh As Worksheet
        Dim rng As Range
        Dim cht As Chart
        Dim strFile As Variant
        Set wsh = ActiveSheet
        Set rng = wsh.UsedRange ' or Selection, or whatever you want
        rng.CopyPicture
        Set cht = Charts.Add
        cht.ChartArea.Clear
        Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wsh.Name)
        cht.ChartArea.Width = rng.Width
        cht.ChartArea.Height = rng.Height
        cht.Parent.Border.LineStyle = 0
        cht.ChartArea.Select
        cht.Paste
        strFile = Application.GetSaveAsFilename(FileFilter:="Image (*.png), *.png")
        If strFile <> False Then
            cht.Export Filename:=strFile, FilterName:="PNG"
        End If
        wsh.Cells(1, 1).Activate
        wsh.ChartObjects(wsh.ChartObjects.Count).Delete
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Monday, October 28, 2019 6:28 PM
  • Try this macro as starting point, you can change it to suit your purpose:

    Sub RangeToImageFile()
        Dim wsh As Worksheet
        Dim rng As Range
        Dim cht As Chart
        Dim strFile As Variant
        Set wsh = ActiveSheet
        Set rng = wsh.UsedRange ' or Selection, or whatever you want
        rng.CopyPicture
        Set cht = Charts.Add
        cht.ChartArea.Clear
        Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wsh.Name)
        cht.ChartArea.Width = rng.Width
        cht.ChartArea.Height = rng.Height
        cht.Parent.Border.LineStyle = 0
        cht.ChartArea.Select
        cht.Paste
        strFile = Application.GetSaveAsFilename(FileFilter:="Image (*.png), *.png")
        If strFile <> False Then
            cht.Export Filename:=strFile, FilterName:="PNG"
        End If
        wsh.Cells(1, 1).Activate
        wsh.ChartObjects(wsh.ChartObjects.Count).Delete
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Hi Hans.

    I tried something like this from some other excel sites. 

    Is there a way to get the "GetSaveAsFileName" to use a pre-defined file name? I ask because this version activates the save window, and asks the user for a file name. I'd like to predefine a user name, based on an iterative naming. 

    ("C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png")

    Where k is the iteration integer. 

    Monday, October 28, 2019 8:50 PM
  • Change the lines

        strFile = Application.GetSaveAsFilename(FileFilter:="Image (*.png), *.png")
        If strFile <> False Then
            cht.EXPORT Filename:=strFile, FilterName:="PNG"
        End If

    to

        strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png"
        cht.EXPORT Filename:=strFile, FilterName:="PNG"

    You'll have to assign a value to k somewhere above these lines.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, October 28, 2019 8:59 PM
  • Change the lines

        strFile = Application.GetSaveAsFilename(FileFilter:="Image (*.png), *.png")
        If strFile <> False Then
            cht.EXPORT Filename:=strFile, FilterName:="PNG"
        End If

    to

        strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png"
        cht.EXPORT Filename:=strFile, FilterName:="PNG"

    You'll have to assign a value to k somewhere above these lines.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    thank you Hans.

    Ok. that's getting me closer, now I have a problem on my side of the code..... 

    right now, my code is selecting the same cell repeatedly. I want it to iterate through each cell, row by row, across several columns, and as it selects each new cell, I want it to snapshot each successive cell, save it through the CopyPicture, allowing me to save the contents of the clipboard (which I'm guessing is the point of the chart itself), and then save each one per the k #. 

    My code is-- 

    Sub PicClip()
    'this code will copy the range selected.
    'create/modify to iterate through each cell, copy,
    'and then past into an individual file, and save by name.
    'not sure if this is possible, but work through it.
    ' 62 columns by 105 rows. 6510 copy/paste "snapshotted cells."
    'this needs to be a for/each cell, and then iterate through rows.
    
    Dim i, j, k As Integer
    For i = 1 To 105
    For j = 1 To 62
    For k = 1 To 6510
    
    Dim wsh As Worksheet
        Dim cht As Chart
        Dim rng As Range
        Dim strFile As Variant
        Set wsh = ActiveSheet
        'Set rng = wsh.UsedRange ' or Selection, or whatever you want
        Set rng = Range(Cells(i, j), Cells(i, j))
        rng.CopyPicture 1, 2
        Set cht = Charts.Add
        cht.ChartArea.Clear
        Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wsh.Name)
        cht.ChartArea.Width = rng.Width
        cht.ChartArea.Height = rng.Height
        cht.Parent.Border.LineStyle = 0
        cht.ChartArea.Select
        cht.Paste
    
    
        
        
        'rng.Select
        strFile = ("C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png")
         cht.Export FileName:=strFile, FilterName:="PNG"
         If strFile <> False Then
            cht.Export FileName:=strFile, FilterName:="PNG"
        End If
        wsh.Cells(i, j).Activate
        wsh.ChartObjects(wsh.ChartObjects.Count).Delete
           
        
        ' "C:\Users\sbuckley\Documents\TR-Icon-State\" & k & ".png"
       
        
    
    Next k
    Next j
    Next i
    
     
    End Sub

    right now, it's iterating through the k, and saving each image, with the renumbering sequence but i and j are remaining on 1. 

    what order do I need to set these in to get it to iterate through the columns, then next row, columns, next row, all while counting k for the renumbering of each new picture? 

    Does k need to be it's own counter? 

    Thank you for your input on this... I REALLY appreciate it. 

    Monday, October 28, 2019 9:20 PM
  • You shouldn't loop through k. Instead, increment k by 1 each time:

    Sub PicClip()
    'this code will copy the range selected.
    'create/modify to iterate through each cell, copy,
    'and then past into an individual file, and save by name.
    'not sure if this is possible, but work through it.
    ' 62 columns by 105 rows. 6510 copy/paste "snapshotted cells."
    'this needs to be a for/each cell, and then iterate through rows.
        Dim wsh As Worksheet
        Dim cht As Chart
        Dim rng As Range
        Dim strFile As Variant
        Dim i As Long
        Dim j As Long
        Dim k As Long
        Set wsh = ActiveSheet
        Set cht = Charts.Add
        Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wsh.Name)
        cht.Parent.Border.LineStyle = 0
        For i = 1 To 105
            For j = 1 To 62
                k = k + 1
                Set rng = wsh.Cells(i, j)
                rng.CopyPicture 1, 2
                cht.ChartArea.Clear
                cht.ChartArea.Width = rng.Width
                cht.ChartArea.Height = rng.Height
                cht.ChartArea.Select
                cht.Paste
                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png"
                cht.EXPORT Filename:=strFile, FilterName:="PNG"
                wsh.Cells(1, 1).Activate
            Next j
        Next i
        wsh.ChartObjects(wsh.ChartObjects.Count).Delete
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SteveDB1 Monday, October 28, 2019 10:27 PM
    Monday, October 28, 2019 9:56 PM
  • You shouldn't loop through k. Instead, increment k by 1 each time:

    Sub PicClip()
    'this code will copy the range selected.
    'create/modify to iterate through each cell, copy,
    'and then past into an individual file, and save by name.
    'not sure if this is possible, but work through it.
    ' 62 columns by 105 rows. 6510 copy/paste "snapshotted cells."
    'this needs to be a for/each cell, and then iterate through rows.
        Dim wsh As Worksheet
        Dim cht As Chart
        Dim rng As Range
        Dim strFile As Variant
        Dim i As Long
        Dim j As Long
        Dim k As Long
        Set wsh = ActiveSheet
        Set cht = Charts.Add
        Set cht = cht.Location(Where:=xlLocationAsObject, Name:=wsh.Name)
        cht.Parent.Border.LineStyle = 0
        For i = 1 To 105
            For j = 1 To 62
                k = k + 1
                Set rng = wsh.Cells(i, j)
                rng.CopyPicture 1, 2
                cht.ChartArea.Clear
                cht.ChartArea.Width = rng.Width
                cht.ChartArea.Height = rng.Height
                cht.ChartArea.Select
                cht.Paste
                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png"
                cht.EXPORT Filename:=strFile, FilterName:="PNG"
                wsh.Cells(1, 1).Activate
            Next j
        Next i
        wsh.ChartObjects(wsh.ChartObjects.Count).Delete
    End Sub


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Bingo! 

    I kept looking at that, and wasn't sure. 

    Thank you.

    One more question.....

    Is there a way to set the format of the k value of the image name to 4 digits? 

    I'm using an old Ron DeBruin Gallery, and it appears to look for 4 digit numbers when searching through the directory of where the files are stored. 

    • Marked as answer by SteveDB1 Monday, October 28, 2019 10:27 PM
    • Unmarked as answer by SteveDB1 Monday, October 28, 2019 10:27 PM
    Monday, October 28, 2019 10:13 PM
  • Change the line

                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png"

    to

                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & Format(k, "0000") & ".png"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by SteveDB1 Monday, October 28, 2019 10:27 PM
    Monday, October 28, 2019 10:21 PM
  • Change the line

                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & k & ".png"

    to

                strFile = "C:\Users\SteveDB1\Documents\My-Icon-State\" & Format(k, "0000") & ".png"


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Perfect!

    This now does exactly what I wanted. :-)

    Thank you very Much Hans. 

    Have a great day. 

    Monday, October 28, 2019 10:31 PM