Results 1 to 8 of 8

VBA to Open a File on ANY Desktop

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    03-23-2008
    Location
    Tennessee
    MS-Off Ver
    Office 2007
    Posts
    706

    VBA to Open a File on ANY Desktop

    Greetings, Gurus.

    I am sending this workbook out for several buyers to use. Part of the macro opens a workbook on their desktop named "Backorder List.xls" and copied data to it. The problem is, the way it is written, I have to go around to each buyers computer and edit it so the vba is looking in the right directory for the file. Is there any way to modify this so that it will open the file "Backorder List.xls" no matter who's desktop it is on?

    Sub Copy_To_List()
        Application.DisplayAlerts = False
        Application.EnableEvents = False
        On Error Resume Next
        Dim wbMyWb As Workbook, wbTemp As Workbook
        Dim filess
        Set wbMyWb = ActiveWorkbook
            
        'Open the workbook Backorder List.xls and set as wbTemp
        If CreateObject("Scripting.FileSystemObject").FileExists("C:\Documents and Settings\jerry.hutcheson\Desktop\Backorder List.xls") = True Then GoTo Exist
        GoTo fin
    Exist:
        ChDrive "C:"
        ChDir "C:\Documents and Settings\jerry.hutcheson\Desktop\"
        filess = Dir("Backorder List.xls")
        Workbooks.Open Filename:=filess
        Set wbTemp = ActiveWorkbook
        Sheets("Backorders").Select
        ActiveSheet.AutoFilterMode = False
        
        'Swap back to wbMyWb to select the Range to Copy
        wbMyWb.Activate
        Range("A7:N" & Cells(Rows.Count, "A").End(xlUp).Row).Copy
        
        
        'Return to wbTemp, paste the data at bottom of existing data.
        wbTemp.Activate
        Range("A65536").End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        
        
        wbMyWb.Activate
        'Copy Vendor # and PO # to Backorder List
        Sheets("Changes").Range("B2:B4").Copy
        wbTemp.Activate
        Range("O65536").End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=True
        Selection.Copy
        Range(Selection, "Q" & Cells(Rows.Count, "A").End(xlUp).Row).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        wbTemp.Close SaveChanges:=True
    fin:
    End Sub
    Thanks in advance for any help you can offer.
    Last edited by [email protected]; 10-20-2009 at 10:22 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1