Results 1 to 7 of 7

Thread: SaveAs to a File with name from Clipboard

  1. #1

    Question SaveAs to a File with name from Clipboard



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all,

    I am stuck in a macro that I am making that automatically formats an excel document. I will have several questions throughout the process I am sure, but I will start with the SaveAs process.

    I would also like to note that I am not new to programming, but I am very new to VBA and there is likely simple answers to my questions, so thank you for your patience.

    So, I am trying to save my file as a specific name that is a portion of a value from a specific cell in the second sheet of the workbook. Here is the code.
    Code:
    Sheets("Sheet2").Select
        Range("F6").Select
            Dim objData As New DataObject
            Dim strTemp As String
            strTemp = ActiveCell.Value
            objData.SetText (strTemp)
            objData.PutInClipboard
    
            Dim DataObj As New MSForms.DataObject
            Dim fileName As String
            DataObj.GetFromClipboard
            fileName = DataObj.GetText
            
            ActiveWorkbook.SaveAs fileName:="C:\Users\a87078\Desktop\Splunk Documents\Splunk" & fileName & ".xlsm", FileFormat:=52, CreateBackup:=False
    Clipboard functions confuse me, as solutions never seem to be consistent on forums. I am getting an error on the "fileName" portion of the last line. I need to remove a portion of the data from the cell aswell, such as "This is the CellValue" and I need it to SaveAs only "CellValue", which I am accomplishing using a SUBSTITUTE() function given that the "This is the " portion is always a constant and doesn't change from workbook to workbook.

    All of this together is proving to be a lot of trouble. Any help would be greatly appreciated. The next step in the macro will be regarding selecting specific ranges of cells based on their contents (as well as an offset cell one to the right of said cell.)

    Thanks!
    Last edited by p45cal; 2017-07-26 at 01:42 AM. Reason: added code tags

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365
    Why use the clipboard at all?!
    This snippet does just the same as your code above:
    Code:
    fileName = Sheets("Sheet2").Range("F6").Value
    ActiveWorkbook.SaveAs fileName:="C:\Users\a87078\Desktop\Splunk Documents\Splunk" & fileName & ".xlsm", FileFormat:=52, CreateBackup:=False
    Now if you want to massage the filename then either do it like:
    Code:
    fileName = Sheets("Sheet2").Range("F6").Value
    fileName = Replace(fileName, "This is the ", "")
    ActiveWorkbook.SaveAs fileName:="C:\Users\a87078\Desktop\Splunk Documents\Splunk" & fileName & ".xlsm", FileFormat:=52, CreateBackup:=False
    or:
    Code:
    fileName = Replace(Sheets("Sheet2").Range("F6").Value, "This is the ", "")
    ActiveWorkbook.SaveAs fileName:="C:\Users\a87078\Desktop\Splunk Documents\Splunk" & fileName & ".xlsm", FileFormat:=52, CreateBackup:=False
    or put it all one one line:
    Code:
    ActiveWorkbook.SaveAs fileName:="C:\Users\a87078\Desktop\Splunk Documents\Splunk" & Replace(Sheets("Sheet2").Range("F6").Value, "This is the ", "") & ".xlsm", FileFormat:=52, CreateBackup:=False

  3. #3
    That is a great help, except for these messages, and the failure to save. Tried all three of your versions.

    "Run-time error '13': Type mismatch" (the type is exact)
    and it highlights the
    "fileName = Replace(fileName, "Quote Name: ", "")" portion in the debugger.
    Not sure where to go from here.

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,607
    Articles
    0
    Excel Version
    365
    They all work here.
    It might depend on what is in Sheet2!F6.
    What is there? A formula, something else?
    Should:
    "C:\Users\a87078\Desktop\Splunk Documents\Splunk"
    be
    "C:\Users\a87078\Desktop\Splunk Documents\Splunk\"
    ?

    Type mismatch is an unusual one for this line. The variable fileName might be causing a problem (it doesn't cause a problem here); you could try changing it so it doesn't clash with Excel's fileName (the argument name), so changing to say fName.
    That means the likes of
    Dim fileName As String
    becomes
    Dim fName As String

    and stuff like:
    fileName = Replace(Sheets("Sheet2").Range("F6").Value, "This is the ", "")
    becomes:
    fName = Replace(Sheets("Sheet2").Range("F6").Value, "This is the ", "")

    and the likes of:
    ActiveWorkbook.SaveAs fileName:="C:\Users\a87078\Desktop\Splunk Documents\Splunk" & fileName & ".xlsm", FileFormat:=52, CreateBackup:=False
    becomes:
    ActiveWorkbook.SaveAs fileName:="C:\Users\a87078\Desktop\Splunk Documents\Splunk" & fName & ".xlsm", FileFormat:=52, CreateBackup:=False
    note that the red fileName DOESN'T change.

    Perhaps if you copy/paste more code here (not the whole module!) so we get an idea of context.
    Last edited by p45cal; 2017-07-27 at 10:44 AM.

  5. #5
    Ah! This must have been the problem, because with the bottom portion edits, it has been fixed and works wonderfully. Thank you so much p45cal; you've been a great help.

    Now, however, I would like to move on to my final portion of the macro, in which I search out and delete certain cells that lie between cells of a certain value (I'll explain more in the link in the comment below, as to keep the post content germane.)

    Thanks again! Check back soon for the new question link.

  6. #6

  7. #7
    Seeker kingcondie's Avatar
    Join Date
    Mar 2018
    Posts
    7
    Articles
    0
    Excel Version
    2016
    @Kohlwebb and @p45cal, Thanks for both the question and answer. It was really helpful!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •