VBA To Determine Filenames Of Open Workbooks
Sep 2, 2009
I have a userform which prompts a user to select two files from the directory. Once these two files are open I will extract data from one into the other, the only problem is these filenames will change and I need to be able to determine what their names are.
I have this code below:
Private Sub CommandButton1_Click()
FiletoOpen = Application.GetOpenFilename _
(Title:="Please choose a file", _
FileFilter:="Excel Files *.xls (*.xls),")
If FiletoOpen = False Then
MsgBox "No file specified."
Exit Sub
Else
Filename1 = FiletoOpen
TextBox1.Value = Filename1
End If
End Sub
If I could somehow extract the exact filename, not full path, from the variables Filename1 and Filename2 I would be fine I guess, I just don't know how to do that.
View 9 Replies
ADVERTISEMENT
Nov 29, 2013
I'm trying to create a VBA macro which will open external workbooks in a specific folder. thes workbooks will have filenames which are identical to the value in cell B1 of my active workbook. Once open, I would use indirect to pull certain results from that external workbook and then close it. I have a separate macro already which runs through the list. I found a few examples online but they don't seem to work.
View 2 Replies
View Related
Sep 15, 2014
I'm trying to open multiple files based on an array of WBnames that are on a 'Dashboard' tab, but I only want to open them if they are not already opened. The code below doesn't seem to check if they are open and just opens everything....
VB:
Sub OpenWorkbooks()
Dim WorkbookOpen()
Dim WBnames() As String 'Array of WorkBooks to be Open
Dim WorkbookCnt As Integer
[Code] .....
View 4 Replies
View Related
May 5, 2006
I have done a search in the forum, and there are many results on browsing directory and opening files, but they are not what I have in mind.
What I have is different folders for different months (e.g. “January”, “February’, “March” and so on), and in each folder I have the same file names (e.g., in “January” folder, I have files called A, B, C. In “February”, I also have files A, B, C but these are different from the files in other folders)
Basically I need a macro which can open a specific list of files.
So first step, the user should be able to browse the directory, then select a folder (no need to open, not sure if it makes any difference though). I need the user to be able to select different folders because the macro should work for the different months in different years.
For this, I have found this
Function GetFolderPath() As String
Dim oShell As Object
Set oShell = CreateObject("Shell.Application"). _
BrowseForFolder(0, "Please select folder", 0, "c:\")
If Not oShell Is Nothing Then
GetFolderPath = oShell.Items.Item.Path
Else
GetFolderPath = vbNullString
End If
Set oShell = Nothing
End Function...........
View 9 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
Oct 19, 2008
If I have a multiple workbooks open and I want to determine if a workbook is open for example "Test 1.xls", how would I do that. I want it to msgbox once to say "open" if open or msgbox "not open" if not open.
View 2 Replies
View Related
Nov 29, 2013
I cannot cut and paste from my working computer to the internet access computer.
This code is intended to check for a workbook already open and if it contains a particular worksheet. This function is called from a routine that reads a master workbook then opens a PCM workbook. The PCM workbook might already be open.
WB_PCM is declared as a workbook and the name is set. WS_PARAMETERS is declared as a string and its name is set to an existing worksheet. The workbook and worksheet do exist, but are not open.
This is the call to the function:
Code:
WS_exists = Is_Workbook_Open( WB_PCM, WS_PARAMETERS_NAME )
And that code is
Code:
Public Function Is_Workbook_Open( workbook_ref As Workbook, sheet_name As String) as Boolean
Dim check_sheet as Worksheet
On Error Resume Next
If( workbook_ref Is Nothing ) Then
Is_Workbook_Open = False
Else
Set check_sheet = workbook_ref.Worksheets( sheet_name ) ' < error
[code].....
During this time the master workbook is open but either not in focus or is minimized within the Excel windows. If I select that worksheet and select a cell in it, then this code runs. What should I do different to get this code to check for WB_PCM being open and being able to handle it when either open or not open.
View 3 Replies
View Related
Jan 1, 2008
I was wondering how i can code a if then loop to determine if a workbook by the name i specify is opened.
If Windows(vCriteria & "ISPR.XLS"). = True Then
MsgBox "true", vbCritical, "Ferguson Enterprises, Inc."
Else
MsgBox "false", vbCritical, "Ferguson Enterprises, Inc."
End If
thats what i got.... not sure if that will work... i'm missing the definition for: If Windows(vCriteria & "ISPR.XLS"). i keep wanting to type isopened, but alas it is not avaliable in the list. There seems to be no extention avaliable that appears to work with such a function. Is it possible to do what i am trying to do?
View 2 Replies
View Related
Apr 6, 2008
Split from Macro To Insert Rows In Protected Worksheet. I have found this code wgich appears to be very close to what i want. Just to re-iterate -
A) i want to detect if any other user has the shared book open, if yes-display msg and exit - it seems to do this by just adding an appropriate msg box
B) if no - open it and lock other users out by temporarily remving share or allowing them in but as read only - not sure if it does that.
Also not sure if it works for a shared file - cannot test as i do not have access to shared network from location.
Function IsFileOpened(StrFilePath As String) As Integer
Dim FileNum As Integer
'First check filepath exists
If Len(Dir(StrFilePath)) > 0 Then
FileNum = FreeFile()
On Error Resume Next
Open StrFilePath For Input Lock Read As #FileNum ' Open file and lock it.
If Err.Number <> 0 Then
IsFileOpened = 1 'File open
Else
IsFileOpened = 0 'File Closed
End If
Close FileNum
Else
IsFileOpened = 2 'File not found
End If
End Function
View 2 Replies
View Related
Jul 10, 2014
How do I determine if AutoFilter is turned on when I open a sheet? I tried FilterMode with no luck.
HTML Code:Â
Sub macro2()
Dim t As Boolean
s = ActiveWorkbook.Name
t = Worksheets("SO_PO_14").FilterMode
End Sub
The FilterMode call always return False,
View 2 Replies
View Related
May 16, 2008
how do you set a return value for a function in VBA? I've looked at examples of functions and I don't understand.
All I want to do is call a function to see if a workbook is open and have it return true or false. Here is the code I have, but it won't let me use go "Return isOpen" at the end like VB would! The IDE complains.
Function IsWorkBookOpen(ByVal name As String) As Boolean
Dim wBook As Workbook
Dim isOpen As Boolean
wBook = Workbooks(name)
If wBook Is Nothing Then
isOpen = False
Else
isOpen = True
End If
'I can't reutrn the value of is open?
Return isOpen
End Function
I've noticed other VBA functions I've looked at don't use "Return"....how does does the function return a value then?
View 3 Replies
View Related
Feb 24, 2014
Trying to manipulate a macro I recorded to be slightly more dynamic with some VBA code. The macro has saved me a lot of time however each month I have to re-do it due to the date changing and I'm sure there must be an easy way to adapt the below code.
Current Code (it repeats afterwards as there are lots and lots of sheets I need to perform the action on).
Windows("Raw Data.xls").Activate
ActiveWindow.DisplayWorkbookTabs = True
Sheets("Calls").Select
Range("C43").Select
Selection.Copy
Windows("Volume Tracking.xlsm").Activate
Sheets("Offline").Select
Range("AE33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
What I'm after is a way to alter the range highlighted based upon matching a cell in "Raw Data.xls' and a cell in "Volume Tracking.xlsm".
The cell A43 in "Raw Data.xls" contains a month (which changes every month); if the month is Jan then I would want the Range to remain as AE33. If it's Feb then I would want the Range to be AF33 (and so on).
In "Volume Tracking.xlsm" I have the months in cells AE4 (Jan), AE5 (Feb) etc.
Is there a way to locate the column in "Volume Tracking.xlsm" on row 4 that contains the value found in A43 in "Raw Data.xls" and then paste what was copied out of "Raw Data.xls" C43 and paste it in to row 33 of the correct column that holds the matching month?
If not could I add code to say if cell A43 in "Raw Data.xls" = Jan then paste to AE33, if Feb then paste to AF33 and so on.........
Excel 2007
View 9 Replies
View Related
Feb 23, 2010
I have a macro on a workbook that when I run it, it's supposed to open a workbook, but it opens another workbook also.
How do I prevent this behavior?
View 9 Replies
View Related
Nov 14, 2006
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
View 6 Replies
View Related
Feb 4, 2014
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.
View 1 Replies
View Related
Nov 14, 2008
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.
View 5 Replies
View Related
Jan 27, 2009
Sub vocab6()
Dim fname As String
fname = "c:Documents and Settings
obertDesktopvocab.xlsx"
Workbooks.Open Filename:=fname
Workbooks(fname).Worksheets("tangible nouns").Range("A1").Value = 9
End Sub
I just want to open a workbook saved on my desktop (from a different workbook), and insert 9 into the first sheet (which is named tangible nouns).
View 9 Replies
View Related
Jun 24, 2009
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.
View 3 Replies
View Related
Dec 2, 2009
I am using a macro to open .txts in excel in seperate workbooks
I look for a macro to save all open woorksbooks in .xls
View 12 Replies
View Related
Dec 28, 2009
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.
View 2 Replies
View Related
Oct 17, 2013
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?
View 2 Replies
View Related
Jul 19, 2007
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.
View 9 Replies
View Related
Jun 11, 2009
I would like to run a macro on all open workbooks except the workbook where the macro is saved. I thought I could just do the following but it does not run my macro on all the work books:
Sub apply()
For Each wb In Workbooks
If wbk.Name ThisWorkbook.Name Then
Debug.Print
nominee_name
End If
Next wb
End Sub
View 9 Replies
View Related
Aug 23, 2007
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?
View 3 Replies
View Related
Feb 25, 2008
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?
View 3 Replies
View Related
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
Else
Set WbN = activeworkbook
N=N+1
End If
This code is contained within a Found files loop
View 2 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
