Results 1 to 2 of 2

Thread: Open 2 files simultaneously......

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2004
    Posts
    4

    Open 2 files simultaneously......

    Hi there. I've got a project in which I will expect VBA will do the following:

    There are two files involved: Masterfile.xls and Tempfile.xls
    The vba codes will be the same in both files.

    1. open a master file and a userform will pop up;
    2. click on a 'duplicate' button to copy the active file (master file) to a temp file;
    3. after creating a copy file, the masterfile will be closed.
    4. a userform (same as the one of the masterfile) will pop up for user to update the record;
    5. value of textbox will be stored in the specified range of the active file (temp file);
    5. click on the 'Update' button to copy the specified range from the temp file to the same range of the masterfile.

    The problem is: The vba codes just failed to copy the specified range from temp file to masterfile. It seemed that after the re-opening of the masterfile, the codes just did nothing.

    The following vba codes are embedded in both the masterfile.xls and tempfile.xls.

    VB Code:
    1. First open the masterfile:
    2.  
    3. Sub auto_open()
    4. Worksheets("sheet1").Activate
    5. If Range("q1").Value = "Y" Then
    6.    Load UserForm1
    7.    UserForm1.Show
    8.    UserForm1.CommandButton1.Visible = False
    9. End If
    10. If Range("q1").Value = "" Then
    11.    Load UserForm1
    12.    UserForm1.Show
    13.    UserForm1.CommandButton2.Visible = False
    14. End If
    15. End Sub
    16.  
    17. Private Sub CommandButton1_Click() 'button to create a tempfile
    18.  
    19. ActiveWorkbook.SaveCopyAs "c:\tempfile.xls"
    20. Workbooks.Open "c:\tempfile.xls"
    21. Worksheets("sheet1").Activate
    22. Range("q1").Value = "Y"
    23. ActiveWorkbook.RunAutoMacros xlAutoOpen
    24. Workbooks("masterfile.xls").Close SaveChanges:=False
    25. End Sub
    26.  
    27. Private Sub CommandButton2_Click() ' a userform will pop up after tempfile is loaded.  This button is to update the record.
    28.  
    29. Dim MasterWB As Workbook
    30. Dim TempWB As Workbook
    31. Dim CopyRange As Range
    32. Dim PasteRange As Range
    33. cntMax = Cells(Rows.Count, 1).End(xlUp).Row 'count the no. of row with data
    34. Range("a" & cntMax + 1).Value = TextBox1.Value
    35. Set MasterWB = Workbooks.Open("C:\Masterfile.xls") 'it seemed that the codes did nothing from here!!
    36. Set TempWB = ThisWorkbook
    37. Set CopyRange = TempWB.Worksheets("Sheet1").Range("a1:a" & cntMax+1)
    38. Set PasteRange = MasterWB.Worksheets("Sheet1").Range("a1:a" & cntMax+1)
    39. Call CopyRange.Copy(Destination:=PasteRange)
    40. MasterWB.Close SaveChanges:=True
    41. End Sub
    42.  
    43.  
    44. Private Sub UserForm_Initialize()
    45. If Range("q1").Value = "Y" Then
    46.    Set TempWB = ThisWorkbook
    47.    CommandButton1.Visible = False 'make 'Duplicate' button invisible
    48. Else
    49.    CommandButton2.Visible = False 'make 'Update' button invisible
    50. End If

    What did I missed?

    Cheers.

  2. #2
    Super Moderator RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,713
    It looks like when you set the tempwb to the thisworkbook object
    you may not be getting the correct thisworkbook object.

    VB Code:
    1. Set MasterWB = Workbooks.Open("C:\Masterfile.xls") 'it seemed that the codes did nothing from here!!
    2. Set TempWB = ThisWorkbook 'May be the ThisWorkBook from the MasterWB object and not the TempWB object.
    So if the thisworkbook is the wrong object then the range you
    are trying to copy may not exist. So when you copy you are
    copying an empty range.

    HTH
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

Posting Permissions

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



Click Here to Expand Forum to Full Width