Load from a desktop

[gmazza76]

Good afternoon,

I have written (borrowed and amended) some code to open a file off my desktop which works fine.
But as several people have access to the main file and can load a file from their desktop I am having issues making this work.

I have created a cell reference "desktop" that mirrors the location dependant upon who is using the file (amendments are made to the desktop based on username) so that it does look like
this is the location I would normally use Set src = Workbooks.Open("C:\Users\THIS CHANGES\Desktop\file.xlsx", but it fails in the code below saying the file cannot be found.

Whats the best way around this as I could make sure the file is saved in a specific location, but using the desktop is easier.

Sub GrabACWAHTData()

Dim lCopyLastRow As Long, lDestLastRow As Long
Dim desktop As String
    
desktop = Worksheets("Calcs").Range("A23").Value

    Application.ScreenUpdating = False
        Sheet2.Visible = True
    
    Set wsCopy = Workbooks("source.xlsm").Worksheets("sheet")
    Set wsDest = Workbooks.Open(desktop)
            wsDest.Parent.Windows(1).Visible = False
    
    lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "P").End(xlUp).Row
    
    lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

    wsCopy.Range("A2", wsCopy.Cells(lCopyLastRow, "A")).Copy
    wsDest.Cells(lDestLastRow, "A").PasteSpecial xlValues
        Sheet2.Visible = xlVeryHidden
            wsDest.Parent.Close True
                Sheets("Welcome").Select
                Range("E11").Select
                Range("E11").Value = Format(Now(), "mm/dd/yyyy hh:mm")

    Application.ScreenUpdating = True
    Application.CutCopyMode = False
End Sub
[code/]

thanks in advance

 

[Domenic]

You can use the Environ function to retrieve the user's name. For example...

Set src = Workbooks.Open("C:\Users\" & Environ("username") & "\Desktop\file.xlsx")

 

[gmazza76]

thank you @Domenic, but I think I have made a mess of this.
Can you confirm if I am thinking correctly at the moment. Using the VBA above the wsCopy is where I am copying the data from and the wsDest is where I should be copying it too?

If so I think I need to copy from the file on the desktop to my open current file and this code is doing it the opposite way round.
How hard is it to change this round?

thanks

 

[Domenic]

Sorry, but I'm confused, can you please restate your question?

 

[gmazza76]

The VB I originally wrote was written I think using code to copy from an open file to a closed one on the desktop. My fault really trying to change someone elses and not writing completely from scratch.

I need to do it the other way round
Copy a range from the sheet on the desktop which is closed into my current open one.

 

[Domenic]

I thought you had an issue with the username to be specified in the path to be used in opening your workbook? Anyway, try...

'open the workbook referenced by the variable 'desktop'
Dim wbCopy As Workbook
Set wbCopy = Workbooks.Open(desktop)

'get the Sheet1 from the opened workbook (change the sheet name accordingly)
Dim wsCopy As Worksheet
Set wsCopy = wbCopy.Worksheets("Sheet1")

'get the destination worksheet (change the workbook and sheet names accordingly)
Dim wsDest As Worksheet
Set wsDest = Workbooks("Book1.xlsm").Worksheets("Sheet1")

Then it looks like you'll need to amend other parts of your code accordingly.

 

[gmazza76]

thank you for this @Domenic originally I did have an issue with the time, but realised I had written the original VBA incorrectly.
The time issue is sorted, I just need to amend the code, hopefully once I have done this I should be sorted