Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    macro to open a file (Excel 2000)

    I am writing a macro to perform several actions. In the first action I have one file open and I need to write a code or another macro that will open a second file. The name of the second file will always change. Once the second file is open, I will then continue to the next action. Can you help me with the code? Thank you.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro to open a file (Excel 2000)

    How should the macro know what file to open next?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    The purpose of the overall macro is to copy data from one file (closed) and paste the data into the destination file (open) I need the macro to pause so the user can select the correct file to open. When the file is open, the macro can resume the rest of the actions. The files to open are all identical in layout, etc. Only the numbers are different. Thank you.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    Is something like this what you are looking for?

    <pre>Option Explicit

    Sub OpenTargetFile()
    Dim vTargetFile As Variant
    Dim wbkTarget As Workbook
    vTargetFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls, All Files (*.*),*.*", _
    1, "Select Target File")
    If vTargetFile = False Then
    Exit Sub
    End If
    Set wbkTarget = Workbooks.Open(vTargetFile)
    End Sub
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    When I paste your code inside my macro, the code displays all on one line in red. The error message "Compile Error: Syntax Error" is displayed. Here is my current code. I need to open a file the line above this action. Thank you.

    Sub CommandButton1_Click()
    '
    ' Sub Interbranch()
    '
    ' InterbranchCopy Macro
    ' Macro recorded 11/09/2004 by bxwallac
    '
    Dim DWB As Workbook
    Dim DestinationBook As String
    Set CWB = ActiveWorkbook
    DestinationBook = Application.GetOpenFilename("All Excel Files,*.xls")
    Workbooks.Open DestinationBook
    Set DWB = ActiveWorkbook
    CWB.Activate
    Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Copy DWB.Sheets("Inter-Branch Allocation").Range("A1216")
    Sheets("Detailed Financials").Range("AA82:BT82").Copy DWB.Sheets("Detailed Financials").Range("AA82")
    Sheets("Detailed Financials").Range("AA95:BT95").Copy DWB.Sheets("Detailed Financials").Range("AA95")
    Sheets("Monthly Plan Summary").Range("Y29:BR29").Copy DWB.Sheets("Monthly Plan Summary").Range("Y29")
    DWB.Activate

    End Sub

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    The instructions in <!post=This Post,425756>This Post<!/post> tell you how to copy code from a lounge post and paste it into your VBE.
    Legare Coleman

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    The code below is modified to open a source file. I did not change the code to do anything with that file as I was not clear what you want to do with it.

    <pre>Sub CommandButton1_Click()
    '
    ' Sub Interbranch()
    '
    ' InterbranchCopy Macro
    ' Macro recorded 11/09/2004 by bxwallac
    '
    Dim DWB As Workbook
    Dim CWB As Workbook
    Dim SWB As Workbook
    Dim DestinationBook As Variant
    Dim SourceBook As Variant
    Set CWB = ActiveWorkbook
    SourceBook = Application.GetOpenFilename(FileFilter:="All Excel Files,*.xls", Title:="Select Source File")
    If SourceBook = False Then
    Exit Sub
    End If
    Set SWB = Workbooks.Open(SourceBook)
    DestinationBook = Application.GetOpenFilename(FileFilter:="All Excel Files,*.xls", Title:="Select Destination File")
    If DestinationBook = False Then
    Exit Sub
    End If
    Set DWB = Workbooks.Open(DestinationBook)
    CWB.Activate
    Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Copy DWB.Sheets("Inter-Branch Allocation").Range("A1216")
    Sheets("Detailed Financials").Range("AA82:BT82").Copy DWB.Sheets("Detailed Financials").Range("AA82")
    Sheets("Detailed Financials").Range("AA95:BT95").Copy DWB.Sheets("Detailed Financials").Range("AA95")
    Sheets("Monthly Plan Summary").Range("Y29:BR29").Copy DWB.Sheets("Monthly Plan Summary").Range("Y29")
    DWB.Activate

    End Sub

    </pre>

    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    I did as you said. I now get the message, "Invisible inside procedure". Here is the code.
    Sub Macro5()
    '
    ' Macro5 Macro
    ' Macro recorded 11/15/2004 by consultant
    '
    Option Explicit

    Sub OpenTargetFile()
    Dim vTargetFile As Variant
    Dim wbkTarget As Workbook
    vTargetFile = Application.GetOpenFilename("Excel Files (*.xls),*.xls, All Files (*.*),*.*", _
    1, "Select Target File")
    If vTargetFile = False Then
    Exit Sub
    End If
    Set wbkTarget = Workbooks.Open(vTargetFile)
    End Sub

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    I believe that the message should have been "Invalid inside procedure" not Invisible.

    You pasted my code inside another procedure. That did two things. First, it put the Option Explicit statement inside your procedure, and the Option Explicit statement must be outside all procedures. Move it outside your procedure. BTW, having this statement will require that all variables be declared with a DIM statement. This can catch all kinds of errors that occur by having such errors as variable names spelled differently.

    Second, you put my "Sub OpenTargetFile()" inside your procedure. You can't have a procedure inside a procedure. Therefore, if you want my code inside your procedure, you will need to remove my "Sub OpenTargetFile()" and the "End Sub" that goes with it.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    I believe this will do it. Thank you. Before I begin the action to copy data, in the newly opened file I want to run an ASAP utility called 'System, Remove VBA Code'. After that I will then unprotect 3 specific sheets. Last step will to be to run the code to copy. Here's the problem. Before I began writing the code, the utility ASAP was up on the menu. Now it is gone. How can I keep it on the menu so it can be included in the overall macro? Or do you have code that would remove VBA code from the newly opened file? I was going to place this code after the line 'End If ' and before the line 'Set DWB = Workbooks.Open(DestinationBook)'. Thank you.

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    Sorry, I don't know anything about ASAP code.
    Legare Coleman

  12. #12
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    ASAP Utilities is a free and easy to use Add-In for MS Excel 97/2000/2002/XP/2003. The utility contains over 300 functions to make your work easier, written by Bastien Mensink. I believe I got this info from one of the Woody posts. Anyway, there is a utility called System, Remove all macros/VBA code from current file. In my macro I first need to open the second file (which you helped me with.) Then, I need to remove all macros/VBA code from the newly opened file. with both files open I then run the macro to copy specific data. If I don't use this utility, do you have code to remove all macros? BTW, You are brilliant! Thank you.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro to open a file (Excel 2000)

    I alos do not know anything about ASAP utilities. Here is some code, though, from Chip Pearson's site to remove code from a workbook, that you could adapt.

    Steve

  14. #14
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    St. Charles, Illinois
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to open a file (Excel 2000)

    Ok, this is the code from Chip that is needed to delete the VBA code in the second file. Can you tell me where to paste it in my macro?
    Code -
    Sub DeleteAllVBA()

    Dim VBComp As VBIDE.VBComponent
    Dim VBComps As VBIDE.VBComponents

    Set VBComps = ActiveWorkbook.VBProject.VBComponents

    For Each VBComp In VBComps
    Select Case VBComp.Type
    Case vbext_ct_StdModule, vbext_ct_MSForm, _
    vbext_ct_ClassModule
    VBComps.Remove VBComp
    Case Else
    With VBComp.CodeModule
    .DeleteLines 1, .CountOfLines
    End With
    End Select
    Next VBComp

    End Sub

    Where do I paste the above code so it will remove the VBA code from the newly opened file before it starts the copy phase of the macro? Here is my macro:

    Sub CommandButton1_Click()
    '
    ' Sub Interbranch()
    '
    ' InterbranchCopy Macro
    ' Macro recorded 11/09/2004 by bxwallac
    '
    Dim DWB As Workbook
    Dim CWB As Workbook
    Dim SWB As Workbook
    Dim DestinationBook As Variant
    Dim SourceBook As Variant
    Set CWB = ActiveWorkbook
    SourceBook = Application.GetOpenFilename(FileFilter:="All Excel Files,*.xls", Title:="Select Source File")
    If SourceBook = False Then
    Exit Sub
    End If
    Set SWB = Workbooks.Open(SourceBook)
    DestinationBook = Application.GetOpenFilename(FileFilter:="All Excel Files,*.xls", Title:="Select Destination File")
    If DestinationBook = False Then
    Exit Sub
    End If
    Set DWB = Workbooks.Open(DestinationBook)
    CWB.Activate
    Sheets("Inter-Branch Allocation").Range("A1216:BI1251").Copy DWB.Sheets("Inter-Branch Allocation").Range("A1216")
    Sheets("Detailed Financials").Range("AA82:BT82").Copy DWB.Sheets("Detailed Financials").Range("AA82")
    Sheets("Detailed Financials").Range("AA95:BT95").Copy DWB.Sheets("Detailed Financials").Range("AA95")
    Sheets("Monthly Plan Summary").Range("Y29:BR29").Copy DWB.Sheets("Monthly Plan Summary").Range("Y29")
    DWB.Activate

    End Sub

    Thank you.

  15. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: macro to open a file (Excel 2000)

    You can keep the code as a separate routine. Then it would seem that you want to add the line:

    DeleteAllVBA

    right after the

    CWB.Activate

    Thus you would activate the "CWB" workbook. Then delete all the code from it. I don't understand why this is an issue however. Copying the info from CWB to DWB should not be copying over any of the macro code from CWB to DWB.

    Or am I missing something?

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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