Macro Stops After Workbooks.Open
May 16, 2007
In the last week the following code has ceased to work, and I do not know why:
If TradeShow = True Then
ReportFile = " ANALYSIS PRINT (CS+T) MASTER.xls"
Else
ReportFile = "ANALYSIS PRINT (CS ONLY) MASTER.xls"
End If
Workbooks.Open Filename:=ReportFile, ReadOnly:=True
Worksheets("TRANSFER DATA").Select
PriceFile = "PRICE COMPARISON.xls"
The Workbooks.Open command executes, and the screen shows the cells of the new spreadsheet. Nothing else happens, and the cursor is sensitive to operator control, showing that Macro control has been lost. I have inserted a test 'MsgBox' after the .Open command, and not even that is executed - so I am certain that the .Open is in some way wiping out the Macro.
View 9 Replies
ADVERTISEMENT
Feb 16, 2009
We're using XP Pro using Excel 07
The background is there are about 40 people who use a Excel based program that contains a pivot and a bunch of other tools that they use on a regular basis, this set of tools has a version number. In this excel workbook, it has a function that looks on the network drive that we have and checks the local version vs the version on our network drive, if its wrong, then the user gets a popup stating your tools are out of date, would you like to update, then they click yes and I have these lines of code
Public Function GetNewTools()
Dim MyFullName As String
'Turn off alerts
Application.DisplayAlerts = False
'Open the new version of tools
Workbooks.Open Filename:= _
"Network DriveUpdate.xlsm"
End Function
The update file has this code that executes on fileopen in the thisworkbook section by calling the following sub
The main issue we're running into is near the very end, the code never makes it to "TEST 2". After the first workbooks close, the code just stops running. No crashes, errors, freezes, anything. It just stops running and never makes it to the second msg box.
View 9 Replies
View Related
Mar 29, 2013
I am trying to cycle ALL open workbooks so that I can choose the file to manipulate in subsequent code this is the code that I am utilizing furhter below
this is my code
VB:
For Each wb In Workbooks
If MsgBox("Do you want to do access this Workbook for the Update " & Chr(10) & Chr(10) & wb.Name, vbYesNo) = vbYes Then
wb.Activate
VI_wb = wb.Name
I = True
End If
Next wb
But for some reason it only loops through .xls and xlm workbooks BUT not .xlsx
is there a way that I can loop through any and all open workbooks.
The reason I do this is because my update are based on many numerous excel workbooks with differnet extentions, and naming conventions and many of the come via email, I cannot use the eact naming convention
View 1 Replies
View Related
Mar 12, 2014
I have a macro to get copy of sheet named "Doc Info" from workbook File 1 to active workbook.
I could do it for one file on any active workbook.
But what I would require is, upon executing this macro , i want this macro to get executed in all open workbooks( could be any numbers ).
I want to move copy of sheet from File 1 to all open workbooks ( which i am doing it manualy for every file )
All these open workbooks could be from any folder , wont be in same folder.
So logic is to execute my macro apply in all open workbooks in my computer.
Below is the code and i have attached file for test
HTML Code:
Sub Copysheet()
Dim wSht As Worksheet
Dim wBk As Workbook
Dim wBk1 As Workbook
Set wBk = ActiveWorkbook 'Workbooks("File 2.xls")
Set wBk1 = Workbooks("File 1.xlsm")
Set wSht = wBk1.Sheets("Doc Info")
wSht.Copy before:=wBk.Sheets(1)
End Sub
View 2 Replies
View Related
Oct 15, 2008
the code necessary to merge all open workbooks (each contains only one worksheet) into 1 workbook made up of all of these individual worksheets?
View 2 Replies
View Related
Apr 14, 2009
i have this macro to save as a csv this macro only save the workbook the you are looking at, i will like for this macro to save as a csv all open workbooks preserving the name of the xls file.
Sub SAVE_AS_CSV()
Dim strFilename As String
strFilename = ActiveWorkbook.Name
strFilename = Left(strFilename, InStr(strFilename, ".")) & "csv"
ActiveWorkbook.SaveAs filename:=strFilename, FileFormat:=xlCSV
End Sub
View 2 Replies
View Related
Jun 20, 2008
I've got a workbook ("Overview") which needs to refer to other workbooks (actually staff timesheets) which sit on a shared drive. All of these are passworded so only the staff member (and myself) can access them
As the workbook opens, it reads the links from the other files, and thus I have to enter the passwords one by one to update the data. I have come up with a macro which should do the job as follows:-
Private Sub Workbook_Open()
MsgBox "OK to run macro?"
' Message is here to let me know if the macro has run
Application.Workbooks.Open "S:LeaveTimesheetsFred 2008.xls", True, False, , "abc", "abc"
Application.Workbooks.Open "S:LeaveTimesheetsBill 2008.xls", True, False, , "def", "def"
Application.Workbooks.Open "S:LeaveTimesheetsTom 2008.xls", True, False, , "ghi", "ghi"
Application.Workbooks.Open "S:LeaveTimesheetsDick 2008.xls", True, False, , "jkl", "jkl"
Application.Workbooks.Open "S:LeaveTimesheetsHarry 2008.xls", True, False, , "mno", "mno"
End Sub
This seems to work, as it opens the workbooks. However, it doesn't run until after I've been manually prompted to enter the passwords. I'm guessing that the workbook links are taking precedence over the macro?
Is there either:-
a) A way to update the data into Overview without having to actually force the timesheets to open?
b) A way to make the macro run before the links update?
(btw I've not compromised the user passwords as Overview is itself passworded, so no-one can view the code!)
View 9 Replies
View Related
Oct 16, 2006
Here is what I'm doing:
-Open .xls file
-Run a macro (it works correctly when run by itself but I'm trying to speed things up)
-Save
-Close
-Open next file, and so on (I'll do this on no more than 200 files at a time all in the same folder)
Here is the code I am using: ....
View 9 Replies
View Related
Feb 12, 2009
I'm just starting out on my journey into VBA and this forum has been a valuable resource for picking up hints and tricks
I've decided to cut some corners and ask for help for the final piece of my current jigsaw - effectively this comes in two bits.
Part one:
I want to copy a sheet from two open workbooks and paste them into my active wookbook. Both source workbooks only have one sheet. I want a dialogue box to select the desired workbook, select and copy all data and then paste to a specified sheet (replacing the current data) in the destination workbook. I then want to select the other source workbook from the dialogue box and copy all data to a separate sheet in the destination workbook. I would like the dialogue box to have two options - Ok to select, copy and paste data, Cancel to end the macro.
Part two:
I want to copy a sheet from my source workbook and paste it as a separate sheet in a new workbook (a one page workbook would be ideal). I then want to save the new workbook in a specified location as "Data - Date" in the format 2009 02 12.
View 9 Replies
View Related
Sep 27, 2006
I'm trying to write a macro that opens selected files, and updates cell F8 to a given value. But if I add more than one file it doesn't work... all in all I want to add 27 files to this macro.
at the moment it is
Sub UpdatePeriod()
'
' updateperiods1 Macro
' Macro recorded 27/09/2006 by navinderm
'
' Keyboard Shortcut: Ctrl+Shift+O
'
Workbooks.Open Filename:="G:....*.xls"
View 3 Replies
View Related
Jun 4, 2008
I am trying to simultaneously use two Excel workbooks that are open at the same time--one is calculations--other is data scenarios...one worksheet = one scenario: A macro uses "Set" to specify workbooks for "current_wb" and "wbkFrom". Code here
Set current_wb = ThisWorkbook
Dat_Fil = Application. GetOpenFilename
Workbooks.Open Dat_Fil
Set wbkFrom = Application.ActiveWorkbook
NOTE-Workbook object variables are declared outside any macro, and as "public." Code here
Public current_wb As Workbook
Public wbkFrom As Workbook
In a second macro I want to use the Current_wb and wbkFrom object variables I set in first macro, but get the following error: "object variable or With block variable not set" for any of the following statements in the second macro
current_wb.Activate
current_wb.Worksheets(1).Select
wbkFrom.Activate
I've declared the object variables as public in a different code module where there is no other code; I've tried declaring as public at the top of the code module that has my macros, but declared at top of code module and outside any macro; I've tried declaring the object variables as "static" variables (instead of "public") within the first macro. I can't figure this out.
View 4 Replies
View Related
May 2, 2014
I currently have a piece of code that opens all of the files in a folder that are called "*agent*", opens them and copies information. Now, these files come with numbers at the beginning which, are always the same. I only want to open certain files that begin with, for example, 801, 802, 803, 804, 805 and 806. How would I write this into my code? As you can see from the below code, it now looks for the files that all have "agent" in the name, but this is opening files that have that name but are not the right ones. Here is my current macro...
[Code] ....
I hope this isnt as simple as putting "MyFile = Dir(MyFolder & "*801*", "*802*")" etc.
View 2 Replies
View Related
Jun 7, 2014
I have 3 workbooks open. 1 is my working file and I name it as "Final[date].xlsm". The other 2 files are my source files which are also open are named as source1.xlsx and source2.xlsx. Both the source files has only one tab/sheet as "Sheet1". My objective is to bring the contents of the source file to my working file in 2 different sheets. all the contents in Sheet1 of "Source1.xlsx" should be paste.values only to the "Final[date].xlsm" with a sheet name "BankDetails". This will enable the user also to replace the old data in "BankDetails" tab. The "source2.xlsx" sheet1 has a different situation. I need to copy only the cells with values, not the entire cells, because it has to be pasted (values only) to range C2:L. I have formulas in the other columns before C and after L.
Take note that my working file name is changing every based on the date while my source file has always same file name.
View 12 Replies
View Related
Jul 25, 2012
I am having an issue with a macro to open password protected workbooks. The macro works just fine in opening the files and whatnot, but for some reason it prompts me to enter the password again once the file has been opened. Funny thing is I can either hit OK or hit Cancel and it goes to the next file. All files open correctly, I was just curious as to why this is happening and how to correct it?
View 1 Replies
View Related
Mar 8, 2013
I have a macro that opens all workbooks from one directory and runs a macro for each workbook to clean up the data. I cannot figure out how to take all those open workbooks and save them to another directory and close the workbook. Also, I do not want the macro workbook (xlsm) to save. I only want it to close. I am working in 2007 Excel.
View 8 Replies
View Related
Dec 3, 2008
I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...
Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.
way to suppress the code in the target workbook I open through workbooks.open
View 9 Replies
View Related
Jul 3, 2007
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.
They are four PC's along with our Managers laptop that run this Macro daily.
About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.
We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.
I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.
I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.
View 9 Replies
View Related
Feb 25, 2009
The macro stops in the last row - it isn't able to select a cell in another sheet than "Menu" (I tested it different variations).
View 9 Replies
View Related
Apr 13, 2014
i ran some tests and it was working good only doing folders with ~100 files. i tried running the macro on all the files (~70,000), but I ran into problems with the computer going into standby while I was away. I tried running a batch of 3000 files and it completed, but only got up to around ~400 out of ~3000 records. Maybe, it stops cause of code related inefficiencies?
Here's the code:
macro_forexcelforum.txt
View 1 Replies
View Related
Dec 2, 2011
I have this macro which sits in the workbook module:
Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Static OldRng As Range
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
OldRng.EntireColumn.FormatConditions(1).Delete
OldRng.EntireRow.FormatConditions(1).Delete
[Code] .......
It's used to highlight the selected/active row.
But, as soon as I run another macro, it simply stops working entirely and I'm left with a row permanently highlighted until you manually remove the conditional formatting. All formats and cell colours controlled in the other macro stop working as well. This is the other (edited for publishing) macro, which is used to send an email, and is found in a module:
Code:
Sub Send_Mail()
'this sends an email that sends a text message
Dim OutApp As Object
Dim OutMail As Object
Dim MyButton As String
Dim strTo As String
[Code] .......
I didn't write the first macro so I'm not sure where the problem is? I'm guessing there is something in the code that is stopping it from working (i.e. running conditional formats) as soon as another macro is run? Is that was is happening?
View 5 Replies
View Related
Dec 10, 2013
I have a macro stored a module at Personel workbook and i am using personel workbook as a hidden one. when i assign a shortcut below macro it is not working but whrn i am press F5 at VBA Editor it is working.
Here is the code:
Code:
Sub Transferdata()
On Error GoTo err1:
Application.DisplayAlerts = False
[Code]....
View 7 Replies
View Related
Mar 12, 2009
I have a problem with the following code
Dim strProcLine As String
With ActiveWorkbook.VBProject.VBComponents(ActiveChart.CodeName).CodeModule
' MsgBox ok
' adaugat procedura goala
.CreateEventProc "Calculate", "Chart"
'MsgBox ok
strProcLine = "Format_Chart"
' MsgBox ok
.InsertLines .ProcBodyLine("Chart_Calculate", 0) + 1, strProcLine
' MsgBox ok
End With
' MsgBox ok
This is part of a larger macro, wich makes 2 pivottables and for each PT a chart, and for each chart i create an even procedure (Chart_Activate) wich calls a procedure to format the chart. Tha macro is alocated to the click even on a button in the sheet where i get the information from.
If i run the macro(click the button) with Microsoft Visual Basic Editor opened all goes ok. But if i close Microsoft Visual Basic Editor and then run the macro it stops right before .CreateEventProc "Calculate", "Chart" of the first chart , and i can't understand why. No error mesages delivered, nothing.
View 9 Replies
View Related
Apr 11, 2013
Problem with the attached Range Overrun.xlsm.
Form opens with set number of rows. End User then enters figure into C3 of how many row are to be added.
VB:
Option Explicit
Dim c As Range
Dim j As Integer
[Code]....
Macro works correctly by adding the number of rows listed in C3. It SHOULD then "name " various cells in the original and new rows.
BUT the Macro goes ON to name cells in rows BELOW the ones that are added.
So on the attached worksheet the original rows were 6 - 9. Rows 10 - 12 were added, but the macro names the cells from Rows 6 - 14.
View 2 Replies
View Related
Feb 6, 2008
i have a worksheet named for example 'allocation 1' this is a master document and is opened and modified and 'saved as' under a customer name. This then stops a few important macros working properly because they refer to the original title and not the new saved title. Is there any code that will let the macro recognise any new title it is saved under?
View 10 Replies
View Related
Jun 10, 2008
I want to make the worksheet protected, however, when I protect the worksheet the macro will not run.
View 14 Replies
View Related
Sep 28, 2011
I've had a macro running for ages, and just found it bugs out when there are spaces in a sheet name.
The user selects a destination cell as part of a form, which passes the variable to the following steps:
Code:
looperx = 0
Do
looperx = looperx + 1
Loop Until Mid(celldestinationstring, looperx, 1) = "!"
Sheets(Left(celldestinationstring, looperx - 1)).Select
The purpose of which is to isolate the sheet name from the cell reference, which is typically like: 'Bob Sheet'!$B$9
This works fine where there are no spaces, but 'dies' with a runtime error when there are. With the apostrophes I assumed it would work around spaces in the string but I can't get it to work.
View 8 Replies
View Related
Oct 2, 2008
Here is the find / replace vba I am using.
Sub Replace()
Cells.Replace What:=Chr(19), Replacement:="-", LookAt:=xlPart
Cells.Replace What:=Chr(24), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(145), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(146), Replacement:="'", LookAt:=xlPart
Cells.Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub
It makes the replacements up to line 37, then stops. If you then delete the first 37 rows and re-run the macro, it again performs replacements, but only on the next 37 rows.
I would like the macro to do the whole sheet...
View 9 Replies
View Related
Jul 10, 2006
I am wirking on a macro which opens a 'sourcefile' and then do some filtering and vlookuping to distribute the data to several files. When i run the macro from the VBA editor, everything runs fine. But if i run it from .xls file, the macro stops after opening the 'sourcefile'
Workbooks.Open Filename:=sourcefile
For i = 0 To 13
'do something...
Next
View 9 Replies
View Related
Oct 20, 2006
Can't run vba macro. I have the following problem with a macro in excel. i wrote it, the macro runs very good from excel /tool/macro/macros/run, i also put a button on toolbar -it works too, i want to assign a keyboard shortcut from tool/macro/macros/options / shortcut and THE MACRO RUN INCOMPLETELY (stops running ).
Actually the macro opens all files in a specific folder, and delete their codes as follows:
Sub delete()
Const strNotFound As String = "There is NO Excel files..."
Dim ffTmp As FoundFiles
Dim wb As Workbook
Dim objVbc As Object
Dim objFile As Variant
Dim lngRet As Long
Application.DisplayAlerts = True
'Search Excel files
With Application.FileSearch
.NewSearch
.LookIn = "C:Corectii\_CS"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks...............
View 7 Replies
View Related
Sep 3, 2007
I am a novice at excel macro programming. My Excel 2000 macro inexplicably stops after the line: Workbooks.Open Filename:=stAddrFullname, ReadOnly:=True
1) I know that the remainder of the code is not executed because a breakpoint on the following line is never reached.
2) I know that this line is executed because the stAddrFullname workbook is clearly opened and is displayed when the code stops prematurely.
3) No error message is displayed and I have no 'OnError' instructions in the code.
4) (This is the bit I really don't understand). If I put a Breakpoint in the macro anywhere in the function preceding the "Workbooks.Open" statement, the macro reaches the Breakpoint and then runs through to completion with no problem. When I remove the breakpoint, it reverts to stopping as soon as it has opened the Workbook.
5) The stAddrFullname workbook has no macros in it.
View 5 Replies
View Related
