• 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...

  • 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

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
 
A code like below can get current user's Desktop path.
Code:
Public Sub GetUserDesktop()
Dim objShell As Object: Set objShell = CreateObject("Wscript.Shell")
Dim strDesktop As String
strDesktop = objShell.SpecialFolders("Desktop")
MsgBox strDesktop
End Sub
 
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
 
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
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.
 
Code:
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
 

Attachments

  • Import Sheet One.xlsm
    16.9 KB · Views: 0
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:
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:
Combine shrivallabha code with your existing ....

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

Code:
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.
 
Hi, ferocious12!
Maybe a bit late but this is another way of referencing the desktop folder path:
Code:
Environ("USERPROFILE")&"\Desktop"
Regards!
 
Back
Top