Free Microsoft Excel 2013 Quick Reference

Closing Another Workbook With VBA

Hi all,

I have a piece of VBA that opens up another workbook when the user opens a document (this is due to using INDIRECT formulae). I'd like for this second workbook to also close when the 'parent' workbook is closed. Can anyone suggest a means of doing this?

FYI - the opening VBA is as follows:

Private Sub Workbook_Open()

Dim Ans As Integer

Ans = MsgBox("This document requires the EDMS Upload Register to be open to calculate some figures. Would you like to open it now?", vbYesNo)
Select Case Ans
    Case vbYes
    Workbooks.Open "U:TrackersEDMSEDMS UPLOAD REGISTER.xls"
   
    
    End Select
    

End Sub
TIA,

SamuelT


Post your answer or comment

comments powered by Disqus
how do I move a macro work book to another workbook with out having to redo the vba? any help. attach is the file I want with the other files but not losing the macro

dear all master.
how are u all.

i have a minor problem about link reference in excel. totally, without code vba, i can do it using link reference for access data from another workbook.
now, problem i found when i moved a workbook and all link reference not work expect i will re insert link reference.

how i can do with vba code for access another workbook without setting manual the link reference?
what is program vba run automate update data when one of workbook is open?(example: workbook1 will get source from workbook2 and connection is using link reference)
hope u can help me..

thx

Hi,

I have a a problem with my VBA.

I try to move all the charts from each worksheets I have in a workbook to another workbook with the same sheets names and these charts have to be copied at the exact same place as in the original workbook.

Here is the sample code :


	VB:
	
 
Dim wb As Workbook, wb1 As Workbook 
Dim wsa As Worksheet 
Dim ch As ChartObject 
Dim chname As String 
Dim myleft As Long 
Dim myright As Long 
 
For Each wsa In wb.Worksheets 
    wsa.Activate 
    If Range("a1").Value = "GR" Then ' (simple check to find which sheets have to be checked for transfer)
        sheetname = wsa.Name 
        For Each ch In wsa.ChartObjects 
            ch.Activate 
            myleft = ch.Left 
            myright = ch.Top 
            ActiveChart.ChartArea.Select 
            [B]chname = ch.Name Not sure it Is working[/B] 
            Selection.Copy 
            wb1.Worksheets(sheetname).Activate 
            Set mysheet1 = wb1.ActiveSheet 
            mysheet1.Activate 
            ActiveSheet.Paste 
            [B]ChartObject.Left = myleft  Not WORKING 
            ChartObject.Right = myright  Not WORKING[/B] 
            Call Delinkchart -> To delink the chart from it 's sources series.
            wsa.Activate 
        Next ch 
    End If 
Next wsa 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I first tried to copy the chart and then select it in the new worksheet using the "name" of the original chart to delink it but It don't work.

Could you help me finding how to select the copied chart in the new sheet and also how to find and use the "location" of the original chart to give the positioning of the new copied chart .....

Many Thanks,

Lobotom1

I need help in creating a macro to Copy rows from one sheet to another sheet in another workbook with all the groups and outline levels.
Thanks in advance.

How do I close a workbook with and without saving in VBA? I know closing a workbook involves the command "Workbooks(filename).close". Thanks!

Hi All,

How do I Copy a sheet in one workbook to another workbook in VBA.

Say copy Sheet1 from workbook1 to Sheet1 to workbook2.

Right now I just do this manually with a copy/paste and use the update
button, but I know there must be a better way.
Thanks As Always!!!

Hi there,

Does anybody know how I can copy a userform I've created in a workbook
to another workbook using VBA? As the user will be able to select the
target workbook, I need to automate this userform export with VBA.

Any ideas? Many thanks for your help!

Kuhni

Grrr...
I hate having to ask simple questions which I can't seem to find answers for on account of the fact they're too simple for anyone else to have posted.

I just need to know how to reference another workbook in order to copy values from cells on it to my own version of it. It's 18 pages, and I need to pull certain columns cell values from certain sheets (these columns are all fixed, no fancy statements required). I'm unfamiliar with the structure required to refer to another workbook. All I need to do is say "This workbook-Sheet1!D2:D20 = Workbook"Feb"-Sheet1!D2:D20", and so on, throughout the sheets.

I did look for solutions to this, but all of the answers are mixed in with complex statements for other simultaneous calculations... I find myself unable to pick out the small piece which I need from the mash of code I'm unfamiliar with.

mew.

I have tried almost everything to close a workbook in vba. This workbook has
content from a running module in Access, and is linked to a table in an
Access database. I set the object to nothing at the end, quit the workbook
even quit the Application with no effect. Error is observed when i attempt
to repopulate the same workbook and the global for the first variable is
exceeded. I also saveas before the closing. Nothing seems to work.

Howdy All,

I am trying to close a workbook in a sub() using the line:

workbooks("thisbk").close savechanges:=false

but I also has a private sub in the 'this workbook before close' section that also contains the same line. This seems to confuse excel as nothing closes. I am trying to avoid using the application.quit avenue as my boss lost an hours work the other day (coz he didnt read the save changes dialog).

Any ideas why this would not be working? Or how to work around it?

Regards
Weasel

I am required to run macro in closed excel workbook using VBA.
pl help

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Hi all,

I have an Excel workbook with VBA macros. If I give my workbook to someone
else to use on a different PC, I get compile errors. The only way out is
ctrl-alt-del.

Bringing up with macros disabled, I see three references that are missing:
funcres, atpvbaen and solver (I am using the solver in the VBA).

I can resolve the missing references on one PC, but then if I transfer it
back to the other PC the references are missing again.

So I tried some auto-open code to re-create all of the references. This
doesn't work apparently because the code can't compile without these
references. Kind of a Catch-22?

Is there anything I can do here to allow multiple users on different PC's to
use my workbook????

The code I run on open (but doesn't) is:

Private Sub Workbook_Open()
Application.Windows.Arrange xlArrangeStyleTiled

Dim wb As Workbook
Dim ReferencePath As String

On Error Resume Next
' Set a Reference to the workbook that will hold Solver
Set wb = ActiveWorkbook

With AddIns("Solver Add-In")
.Installed = False
.Installed = True
End With

With AddIns("Analysis ToolPak")
.Installed = False
.Installed = True
End With

With AddIns("Analysis ToolPak - VBA")
.Installed = False
.Installed = True
End With

ReferencePath = Application.LibraryPath & "SOLVERSOLVER.XLA"
wb.VBProject.References.AddFromFile ReferencePath

ReferencePath = Application.LibraryPath & "Analysisfuncres.XLA"
wb.VBProject.References.AddFromFile ReferencePath

ReferencePath = Application.LibraryPath & "Analysisatpvbaen.XLA"
wb.VBProject.References.AddFromFile ReferencePath

End Sub

Hi there,

How can I bypass the Workbook_Open event when opening a workbook with
VBA.

Holding down the shift key does this when opening it manually. The
open event simply brings up a message box which normally a user would
just click OK.

I have tried Sendkeys, but this does not work. Can't find anything in
the help files.

Any suggestions welcome.

Cheers

Paul

Hello Everyone,

Hope someone can help me with this issue.
I use the below code to extract data from another workbook "B1" which runs a macro when workbook is closed. The particular code that runs before "B1" closes, causes excel to hang. If I remove the code from Worksheet "B1" it works fine. Is there a way to bypass/disable the code from "B1" running from the below code. Please note, that I only need the data extracted from "B1" so I dont need any codes to be run on that workbook while the below code is run.

Thanks in advance for any help.

Regards,

XL Factor.


	VB:
	
 IMPORT_Click() 
    On Error Resume Next 
     
    Dim fn As Variant 
    Dim thisWB As String, newWB As String 
    Dim targetSheet As String, destSheet As String 
     
    targetSheet = "Todays Movements" 
    destSheet = "B1 Movements" 
    thisWB = ActiveWorkbook.Name 
     
    fn = Application.GetOpenFilename("Excel-files,*.xls*", 1, "Select your data file", , False) 
     
    If TypeName(fn) = "Boolean" Then End 
    Debug.Print "Selected file: " & fn 
     
On Error Goto end_sub: 
     
    Application.ScreenUpdating = False 
    Workbooks.Open fn 
    newWB = ActiveWorkbook.Name 
    Application.Windows(thisWB).Activate 
    Sheets(destSheet).Range("A3:O3").Select 
    Range(Selection, Selection.End(xlDown)).Select 
    Selection.ClearContents 
    Sheets(destSheet).Range("A3").Select 
    Workbooks(newWB).Sheets(targetSheet).Activate 
    Workbooks(newWB).Sheets(targetSheet).Range("A3").Select 
    Workbooks(newWB).Sheets(targetSheet).Range("A3:M300").Select 
    Selection.Copy 
    Application.Windows(thisWB).Activate 
    Sheets(destSheet).Range("A3").PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
    Range("M3:M300").Select 
    Selection.Copy 
    Range("O3").PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
    Range("M3:M300").Select 
    Selection.ClearContents 
    Range("A3").Select 
    Workbooks(newWB).Close 
     
end_sub: 
    Application.ScreenUpdating = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi, (am still a newbee at VBA)

I have a workbook that gets the users information and then on lookup information, loads and activates another workbook with a menu userform.

How do I get the new workbook userform to show after closeing the previouse workbook.

Code I have used:


	VB:
	
 Workbook_Open() 
    CloseStartFile 
    Load AgentsMenu 
    AgentsMenu.Show 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
In a seperate module:


	VB:
	
 CloseStartFile() 
    Workbooks("agents.xls").Worksheets("Agents").Range("A1") = Workbooks("start.xls").Worksheets("Start").Range("A1") 
    Workbooks("start.xls").Close SaveChanges:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This closes the first workbook (start.xls), but does not show the userform (AgentsMenu).

Any help would be appreciated.

Thanks,

Delano S

I have a worksheet with a macro that copies the worksheet into another workbook and renames it in that workbook. The only problem is, there's worksheet-name specific code in the macros of that worksheet (Worksheet_Calculate and Worksheet_Change events) that make the copy-macro crash. I've been fighting with it for too long and I was wondering if there was some way I could copy the worksheet over to the new workbook without the VBA attached?

Hi all

I have a quick query which I would appreciate any help with. I am using:

ActiveWorkbook.Close False

to close a workbook, but the macro keeps stalling to ask me if "I want to retain the data on the clipboard to paste into another document later" - you know the message...

the macro doesn't continue until I click yes or no - so my question is: is there any way I can amend that line of code so that it closes the file without prompting (incidentally, I don't need the data on the clipboard if it makes a difference)

Any help is appreciated,

Rich

Hello all,

I have a workbook (workbook 1) in which I am performing a
vlookup calculation using another workbook (workbook 2).
In the macro, I want to open workbook 2, perform the
calculation, and then close workbook 2. Anyone have any
ideas?

Any help is appreciated.

Thanks,

Trevor

Hi folks,

may be I am too stupid to find the right google search arguments for my
problem.
Here goes:

EXCEL 97 Workbook on Windows 2000 prof. machine with VBA code in Module
1. Works as intended.

Now I hide the VBA code via VBA project properties and save ok.
Subsequent attempts to save the workbook will store it under a new
(random ?) filename without ".xls". An associated message will tell me
so as well as that the
file cannot be renamed and I should close this file.

What am I missing ?

Thanks in advance
--
Friedrich Hofmann
[email protected]

Hi guys,

Can I go through (1 by 1) a series of workbooks in the same folder just as
I can go through a series of worksheets one by one in a workbook.

I have a spreadsheet for every day (about 6 months worth) and each
individual workbook is named something like thur-13-apr.

I need to open each in turn and do some changes to them all. The changes
can be done with vba as they are all formatted the same inside but I want
to open each with a macro (if possible) and then close when the work is
done.

I don't want to have to open each manually if possible.

Is this possible?

Ron

I would like to know if it is possible to open an excel workbook from VBA. It would also be useful to close it too.

If anyone can help that is great. The path will always be the same.

Trev.

I've got two workbooks "Invoice" and "Database".
I've managed to get them to update each other using VBA but the one named Invoice is one of many with different names.
So.. because I'm running the macro contained in the Database workbook, from the Invoice workbook, I'm trying to refer to the Invoice workbook by using something like the AWB function below but Excel doesn't like what I'm doing.
Ant ideas or help would be much appreciated.

The macro below works ok but I need to refer to various workbooks and only one of them is named Invoice.

Sub DBupdate()
With GetObject("C:Invoice.xlsm")
Workbooks("Database").Sheets(3).[A2:D500] = .Sheets(3).[A2:D500].Value
Workbooks("Database").Close True
End With
End Sub
This function returns the name and path of the active workbook that I'm running the macro from, even though the macro is contained in the Database workbook.

Function AWB() As String
  AWB = ActiveWorkbook.Path & "" & ThisWorkbook.Name
End Function
Compare the working macro above with the code below and it's obvious what I'm trying to do.

Sub DBupdate()
With GetObject("ActiveWorkbook.Path & "" & ThisWorkbook.Name")
Workbooks("Database").Sheets(3).[A2:D500] = .Sheets(3).[A2:D500].Value
Workbooks("Database").Close True
End With
End Sub


Below is the macro I am trying to execute. The master workbook opens another workbook (very large with its own macros at open), then gathers some data, and then suppose to close the workbook, to open the next. Everything works except closing the workbook, if i comment out the close argument I have to stop every 10 workbooks to close them for memory issues. I have tried several attemps listed below. I started with a full set of hair, now have none.....

Public xlDM As
Excel.Workbook
'attempt 6
'     did not want to load spreadsheet at all
'     Public xlDM As Excel.Workbooks

Sub TestClose()

'attempt 5
'     putting it as a private variable did not work
'     Dim xlDM As Excel.Workbook
    

    directory = "C:Documents and SettingsMy DocumentsDeal ModelsFY12Q2closedFor ReportOld DM and PC"


    Set xlDM = Workbooks.Open(directory & "" & "Test.xlsm")
    MsgBox "Made it here"
'attempt 1
'    xlDM.Close
'    this hangs up and after I press escape and kill the macro it asks me if I want to save before closing
'attempt 2
'    xlDM.Close SaveChanges:=False
'    this closes the workbook but hangs up, i have to press esc and then end.  I never make it to the end
'attempt 3
'    Workbooks(2).Close
'    this hangs up and after I press escape and kill the macro it asks me if I want to save before closing
'attempt 4
'    Workbooks(2).Close SaveChanges:=False
'    this closes the workbook but hangs up, i have to press esc and then end.  I never make it to the end
'attempt 7
'    Workbooks("Test.xlsm").Close SaveChanges:=False
'    this closes the workbook but hangs up, i have to press esc and then end.  I never make it to the end
'attempt 9
'    ActiveWorkbook.Close savechanges:=False
'    this hangs up and after I press escape and kill the macro it asks me if I want to save before closing
'attempt 8
'    Windows("test.xlsm").Activate
'    ActiveWindow.Close
'    this hangs up and after I press escape and kill the macro it asks me if I want to save before closing
'attempt 10
'    Windows("Test.xlsm").Activate
'    Range("A1").Select
'    ActiveWindow.Close
'    this hangs up and after I press escape and kill the macro it asks me if I want to save before closing


    MsgBox "It closed!"

End Sub


I've just recently begun experimenting with the possiblities of macro.

I'm wondering if it is possible to export att row af cells, say B5:B13 to another workbook? This workbook is however supposed to be growing, and I want the exported the cells to be placed in the first empty row in the specified dokument.

I want to do this with an action-button, but locking the macro to a button is something I should be able to do myself.

If it is possible, I'd appreciate any help about how it could be done.

I also have another question.

In the same row that i want to export, I'd like to create a Hyperlink to active workbook ( the one which I intend to export from).

Please note that it the workbook used is intended to have a namechange before the hyperlink is created. And I want the hyperlink to link to the newly saved file.

Is this possible to perform?

Thank you very much!


No luck finding an answer? You could always try Google.