Set Variables To All Open Workbooks Where Name End With..
Mar 15, 2008
I have a folder containing a number of worksheets, these worksheets are of 2 types, a customer submitted workbook and, associated with each of these, anything between 2 and 7 locally created workbooks. I have a macro which opens a customer workbook and all the relevant local workbooks. My problem is that I need to declare a variable for each of the workbooks in the form
Dim N As Integer, WbN As workbook,Wb1 As workbook
If activeworkbook. name Like *ALL.xls Then
Set wb1 = activeworkbook
Set WbN = activeworkbook
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
I have create a nice invoice with macros to random insert pages and summations etc. Works fine, but to complete it I want to go one step further and there ìt got out of hand. I want to create the following, but I can not get al this combined in two Macros.
Basis: Workbook 1 = Invoice, Workbook 2 = Invoicelist Required actions Macro1: 1. Starting point is that Workbook “Invoice” is open. 2. The user activates Macro1. 3. Check if Workbook “Invoicelist” is active. 4A. If YES then continue, 4B. If NOT then reply with some sort of Alertbox “Invoicelist not active! Click OK to open (needs to open Workbook “Invoicelist” or Cancel to stop (needs to stop Macro). 5. Workbook 2 “Invoicelist” is now open. 6. Find the last invoicenumber in the list. Add “1” and put the new number to the list (to prevent using double numbers). Then I want to take this number to the “Invoice’ workbook, but that can be done by copy and paste.
Now I get really stuck because I want to use variables instead of multiple copy paste actions.
Required actions Macro2: 7. We are in Workbook “Invoice”, the user activates Macro2. 8. Same check if Workbook “Invoicelist” is active as in Macro1. Several data from “Invoice” has te be transfered to “Invoicelist”. And to be safe, this does not have to be the last cell if the user “forgot” to store date for a previous invoice! 9. Store data from sevaral cells in variables, including invoicenumber! 10. Find invoicenumber in “Invoicelist”. 11. Paste data from variables into fields on same row as correct invoicenumber. 12. Save Workbook “Invoicelist”. 13. Go back to Workbook “Invoice”.
The two problems I cannot fix is checking if Workbook is Active and store and use cell values with variables.
I have a UDF in a workbook that works fine until I open another workbook which contains the same UDF. When Excel does a full recalculation it not only recalculates the currently selected workbook but also recalculates all other open workbooks. Something from the currently selected workbook appears to interfere with the other open workbooks as these other workbooks show #VALUE! in all cells that use the UDF. Is there some way to make a UDF unique only to the workbook that it resides in such that opening another workbook with the same named UDF won't interfere with it?
Function SumRangeLookup(FromCode, ToCode, Database, FromColumn, ToColumn) Dim Code As Range Dim MonthColumns As Integer Dim CalcResult As Double SumRangeLookup = 0 For Each Code In Range(Database) On Error Goto SkipCode If Code >= FromCode And Code <= ToCode Then For MonthColumns = FromColumn To ToColumn CalcResult = CalcResult + Code.Offset(0, MonthColumns) Next MonthColumns End If Next Code SumRangeLookup = CalcResult SkipCode: End Function
I have a workbook open. When I try to open a separate workbook, it opens already opened workbook (basically linking the workbooks as one). I want to have my workbooks on two separate screens this is preventing that. How to unlink these workbooks.
Having some issues moving between 2 open workbooks. I just want to add a new workbook, select the first workbook and active sheet then move that sheet to the new workbook that was just made and lastly save the new workbook with the name of the sheet that was just moved. I have some code but it is not working correctly.
I have a script below that opens all the workbooks in a folder based on a msgbox. The script works as is. However what I would like to do is remove the msgbox and have the script go back and forth between the folders.
Example it would open one workbook in the (Large Area)folder call my other script (Hazleton_Data_Conversion) Return Open a workbook in (Varsity) Folder Call the my (Hazleton_Data_Conversion)and repeat the process till all the workbooks in both folders have been processed.
Like I said this script works with the msgbox I just like to change to without msgbox and to flip back and forth.
I have an excel sheet that has a userform on, when this is open my users are required to cross reference with another excel sheet they have just typed up to make sure they enter the right information into the userform. The only problem i have is, when the userform is open my users cant access any other excel workbooks.
I have a macro that opens all workbooks in a folder and searches for a few terms returning the appropriate rows, my problem is that someone has decided to protect some of the sheets so when the files are opened you get the password box popup. I have added the (filename, readonly) segment however this has not solved the issue, the macro displays an error stating a workbook is open.
Is there any way to resolve this without unprotecting the sheets?
I am trying to use Workbooks.Open() function in a function. The function is not working as desired (it does not return workbook object), but when i use same piece of code in sub(macro), it works fine. I am not sure if I cant use this API inside function. I am using function because I would like to call this inside cell and that should return some value.
I have written a VBA code to make a pivot table from data in another workbook,A, and transfer it to workbook B. I however dont want to restrict this code to workbook A only. This is because usually workbook A will have varying names everytime it is sent. I want to be able to get data from any other workbook that is open at the same time as work book B. What code can I use to select/activate another unspecified open workbook and get data from there?
I made an Excel based program that uses multiple workbooks with two main workbooks (“Master List” and “Products”) that all the others pull information from. I run into a problem when a user clicks the close X in the upper right hand corner and the entire application closes. I found some code that will let me close all the workbooks but one (the code is put in the “Products” workbook in the BeforeClose event).
For Each wb In Workbooks If Not wb Is ThisWorkbook Then wb.Close SaveChanges:=True End If Next wb
Is there a way to modify the code so it will close all the workbooks but the two main ones if someone clicks the close X button in the upper right hand corner?
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
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
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
create a list of all open workbooks? I've found various examples of code that will do this in a Message Box, but what I am after is for them to be listed actually in the worksheet (let's say in column AA).
Code: Sub Macro1() ' Sheets("Log").Select Range("F1").Copy For Each wb In Workbooks If wb.Name Like "Test Book*" Then wb.Activate Sheets("Data").Select Range("O2").Select ActiveSheet.Paste
can't get it to work properly...Basically I want the code to copy the date in cell F1 of the 'Log' sheet in Workbook 'Main' - then goto an open Workbook called 'Test Book' (note: this Test Book is a partial string name used hence the other code around it) and paste the copied date into cell O2 in sheet 'Data'
Using this pasted date in cell O2 carry out the required filter function.
The copy/paste of the date from my Workbook Main to Test Book isn't working so the code then shows error when trying to filter the data using the pasted date.
I am trying to get the caption that is displayed on each window of all the open workbooks. I am using the code below to loop through all the workbook windows, get their handles and finally display their respective captions on a Msgbox.I am having 2 problems:
1- The code loops only once instead of once for each open workbook !
2- The Msgbox displays an empty string instead of the workbook caption !
If I am not mistaken, problem 2 is due to the fact that the Class name"EXCEL7" used in the code refers to a worksheet and not to a workbook. The trouble is I can't figure out what a Workbook Class name is so that I can use it in the FindWindowEx Function
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _ (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long[code]......
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"
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!)
I've been developing a form in excel (not userforms, just the usual conditional formatting, validation and macros to show/hide rows etc). It all works fine now except one thing was noticed when a colleague was testing it. If she opens the form from the email, and she already has excel open, then the form will not work when she makes a selection from the dropdown list, which unhides the rest of the form. If she closes down all of excel and opens the form from fresh, it works fine.