1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How can I refer to file saved on desktop in code

Discussion in 'VBA Macros' started by ferocious12, Apr 15, 2017.

  1. ferocious12

    ferocious12 New Member

    Messages:
    17
    Hi,

    I have to open a file saved on a desktop of the user and copy worksheet 1, close without saving and paste the copied worksheet in the active workbook containing macro.

    Can you help me with the macro as user name used as part of the path of file to be opened changes as the macro is used by new user.


    Thanks
  2. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,591
    A code like below can get current user's Desktop path.
    Code (vb):

    Public Sub GetUserDesktop()
    Dim objShell As Object: Set objShell = CreateObject("Wscript.Shell")
    Dim strDesktop As String
    strDesktop = objShell.SpecialFolders("Desktop")
    MsgBox strDesktop
    End Sub
     
    RAM72 and Derek McGill like this.
  3. ferocious12

    ferocious12 New Member

    Messages:
    17
    Thanks shrivallabha.

    H can use this with workbooks.open ......

    Because this is giving me a msg box with the path to desktop but I want to open a workbook without seeing this msgbox saved on a desktop
  4. shrivallabha

    shrivallabha Excel Ninja

    Messages:
    1,591
    That is demo code to retrieve path. You have to adopt it into your code. If you don't know how to go about it then post your current code in the Workbook_Open sub.
  5. Logit

    Logit Member

    Messages:
    32
    Code (vb):


    Option Explicit

    Sub CopySheet()

    Dim VipFile As String
    Dim wbSource As Workbook 'vip file
    Dim wbTarget As Workbook 'this file

    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Please select a file"
    .ButtonName = "Select"
    .InitialFileName = "C:\"
    If .Show = -1 Then
    VipFile = .SelectedItems(1)

    Set wbTarget = ActiveWorkbook 'define variable
    Set wbSource = Workbooks.Open(VipFile) 'DEFINE variable for source

    '///// Other workbook first sheet name is Sheet1 ... or edit as required \\\\\

    wbSource.Sheets("Sheet1").Copy After:=wbTarget.Sheets("End") 'copy sheet NAME after sheet NAME
    wbSource.Close

    Else
    MsgBox "You did not select a File!"

    End If
    End With

    End Sub

     

    Attached Files:

  6. ferocious12

    ferocious12 New Member

    Messages:
    17
    Many thanks. I have pasted my code with narrative where I need help.

    I don't want to select the file manually as the file is saved on the desktop of each user code should find the path itself :)

    Code (vb):
    Sub CopyFilter()

    ' open Monthly report workbook saved on desktop of the user (changes everytime - need help to get to select desktop path)
    Workbooks.Open "C:\Users\xxxxx\Desktop\Monthly_Report.xlsx"

    ' copy the only sheet saved in Monthly report workbook

    Sheets(1).Copy

    ' Activate Workbook Macro (this contains this macro) & paste the sheet (need code for that)
    Workbooks("Macro.xlsm").Paste

    ' Following code separate the contents of column E and separate the names separated by comma to separate columns starting column L

        Dim N As Long, wf As WorksheetFunction
        Set wf = Application.WorksheetFunction
        N = Cells(Rows.Count, "E").End(xlUp).Row
        Dim i As Long, j As Long, k As Long
        For i = 1 To N
            ary = Split(wf.Trim(Cells(i, "E").Text), ",")
            k = 2
            For j = LBound(ary) To UBound(ary)
                Cells(i, k).Value = ary(j)
                k = k + 1
            Next j
        Next i
     
     
     
    ' need code here that create a pivot in another worksheet and only include rows from existing worksheet where column L is blank (need help)
    ' need code here that create a pivot in another worksheet and only include rows from existing worksheet where column K is blank (need help)

     
    End Sub
    Last edited: Apr 16, 2017
  7. ferocious12

    ferocious12 New Member

    Messages:
    17
  8. Logit

    Logit Member

    Messages:
    32
    Combine shrivallabha code with your existing ....

    Paste this at the top of your existing macro to open the Users workbook on their desktop:

    Code (vb):

    Sub CopyFilter()

    ''open Monthly report workbook saved on desktop of the user (changes everytime - need help to get to select desktop path)
    ''Workbooks.Open "C:\Users\xxxxx\Desktop\Monthly_Report.xlsx"

    Dim objShell As Object: Set objShell = CreateObject("Wscript.Shell")
    Dim strDesktop As String
    strDesktop = objShell.SpecialFolders("Desktop")
    MsgBox strDesktop
    ' open Monthly report workbook saved on desktop of the user (changes everytime - need help to get to select desktop path)
    Workbooks.Open strDesktop & "\Monthly_Report.xlsx"
     
    The remainder of the code you'll need to get a more knowledgeable person to assist you with. My knowledge of pivot tables is less than nothing.

    Sorry.
  9. ferocious12

    ferocious12 New Member

    Messages:
    17
  10. Logit

    Logit Member

    Messages:
    32
    You are welcome.

    Cheers.
  11. SirJB7

    SirJB7 Excel R┼Źnin

    Messages:
    8,379
    Hi, ferocious12!
    Maybe a bit late but this is another way of referencing the desktop folder path:
    Code (vb):

    Environ("USERPROFILE")&"\Desktop"
     
    Regards!

Share This Page