macro to open a file (Excel 2000)

Topic

New Reply

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.

Reply | Quote

Replies

How should the macro know what file to open next?

Steve

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

How should the macro know what file to open next?

Steve

Reply | Quote

Is something like this what you are looking for?

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

Reply | Quote

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

Reply | Quote

The instructions in This Post tell you how to copy code from a lounge post and paste it into your VBE.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

The instructions in This Post tell you how to copy code from a lounge post and paste it into your VBE.

Reply | Quote

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.

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

Reply | Quote

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.

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

Reply | Quote

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.

Reply | Quote

Sorry, I don’t know anything about ASAP code.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

no, you are not missing anything. My co-worker just wanted the VBA code cleared. Thank you.

Reply | Quote

Got it. My problem is I want to delete all VBA code from the destination file before the action to copy a range. This doesn’t work. Thank you.
‘ 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
DeleteAllVBA
Sheets(“Inter-Branch Allocation”).Activate
ActiveSheet.Unprotect
Sheets(“Detailed Financials”).Activate
ActiveSheet.Unprotect
Sheets(“Monthly Plan Summary”).Activate
ActiveSheet.Unprotect
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
ActiveWorkbook.ChangeLink Name:=”Interbranch 11-16-2004 Cindy.xls”, NewName:=DWB.FullName, Type:=xlExcelLinks

End Sub

Reply | Quote

Could you be more specific about what doesn’t work?

I created a testfile, added some code and saved it. I uncommented your sub line (I don’t understand why the sub line is commented unless you have another sub line that you didn’t copy) and ran the code. I opened the testfile, and it deleted all the code in the file after it was opened.

Are you getting error messages? Is it deleting code from the wrong workbook? What is the symptom? “Doesn’t work” is pretty vague.

Have you created a reference to the “extensibility library” as described by Chip Pearson on the page I referenced? [fifth paragraph from the top]

Steve

Reply | Quote

I may have misunderstood the original question or the code, but>>>>

I understood the original poster wanted to start with the active workbook (CWB), open a 2nd wbk (DWB), delete all VBA from the DWB, then copy 3-4 ranges from the CWB into the DWB and finally close the DWB and move on to a new DWB where the procedure would be repeated.

The code appears to have a 3rd wbk involved SWB as the source of the ranges to be copied. — No sure if this is what is wanted.

Also, placing the DeleteVBA code after CWB.Activate will delete the code from the original workbook. BE VERY CAREFUL HERE !! ARE YOU SURE THIS IS WHAT YOU WANT?

Reply | Quote

I may have misunderstood the original question or the code, but>>>>

I understood the original poster wanted to start with the active workbook (CWB), open a 2nd wbk (DWB), delete all VBA from the DWB, then copy 3-4 ranges from the CWB into the DWB and finally close the DWB and move on to a new DWB where the procedure would be repeated.

The code appears to have a 3rd wbk involved SWB as the source of the ranges to be copied. — No sure if this is what is wanted.

Also, placing the DeleteVBA code after CWB.Activate will delete the code from the original workbook. BE VERY CAREFUL HERE !! ARE YOU SURE THIS IS WHAT YOU WANT?

Reply | Quote

Could you be more specific about what doesn’t work?

I created a testfile, added some code and saved it. I uncommented your sub line (I don’t understand why the sub line is commented unless you have another sub line that you didn’t copy) and ran the code. I opened the testfile, and it deleted all the code in the file after it was opened.

Are you getting error messages? Is it deleting code from the wrong workbook? What is the symptom? “Doesn’t work” is pretty vague.

Have you created a reference to the “extensibility library” as described by Chip Pearson on the page I referenced? [fifth paragraph from the top]

Steve

Reply | Quote

Got it. My problem is I want to delete all VBA code from the destination file before the action to copy a range. This doesn’t work. Thank you.
‘ 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
DeleteAllVBA
Sheets(“Inter-Branch Allocation”).Activate
ActiveSheet.Unprotect
Sheets(“Detailed Financials”).Activate
ActiveSheet.Unprotect
Sheets(“Monthly Plan Summary”).Activate
ActiveSheet.Unprotect
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
ActiveWorkbook.ChangeLink Name:=”Interbranch 11-16-2004 Cindy.xls”, NewName:=DWB.FullName, Type:=xlExcelLinks

End Sub

Reply | Quote

Cindy, you didn’t ask, but does your co-worker know that you can password protect code so that other users can’t view it? In the VBE, right click the VBAProject, select VBA Project Properties, Protection, check Lock Project for Viewing, and provide a password. Project security can be beaten only by a fairly obscure hacking technique, so for most work I’d trust it.

Reply | Quote

Cindy, you didn’t ask, but does your co-worker know that you can password protect code so that other users can’t view it? In the VBE, right click the VBAProject, select VBA Project Properties, Protection, check Lock Project for Viewing, and provide a password. Project security can be beaten only by a fairly obscure hacking technique, so for most work I’d trust it.

Reply | Quote

no, you are not missing anything. My co-worker just wanted the VBA code cleared. Thank you.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Cindy,
Just for future reference, if you want to call any ASAP code from your own code, you can set a reference to the ASAP Utilities add-in (via Tools-References) and then call the procedure as normal – in your current example that would be something like:

   DWB.Activate
   Call ASAPUtilities.RemoveAllVBA

Reply | Quote

Cindy,
Just for future reference, if you want to call any ASAP code from your own code, you can set a reference to the ASAP Utilities add-in (via Tools-References) and then call the procedure as normal – in your current example that would be something like:

   DWB.Activate
   Call ASAPUtilities.RemoveAllVBA

Reply | Quote

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.

Reply | Quote

Sorry, I don’t know anything about ASAP code.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Is something like this what you are looking for?

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

Reply | Quote