Activate Windows Or Workbook Functions

Jul 22, 2009

I have .xls files (ex: a.xls, b.xls etc) in a shared drive.How do I get information (run macros without changing anything) from a.xls and create a new file in my hard drive without opening a.xls. I don’t know if that is possible to do. May be sounds very weird. For example, I name this file on my hard drive as a01.xls, b01.xls from b.xls. Now I run other macros from a01.xls. which is active. I want this macros to run independent of the file name the user creates. In otherwords the workbook has to be variable. Not sure how to use ‘Thisworkbook’ function if that is what is needed to do.

View 12 Replies


ADVERTISEMENT

Windows.activate Not Working...

Dec 11, 2006

I have the below VBA code that I'm working on where I run it after I already have a workbook entitled BRNewBusiness121106.xls (or whatever day it is when I'm running the code) that's open, and the code opens another workbook, BR-MasterAccounts.xls and then does some work between the two workbooks.
The code is stopping with a "subscript out of range" error at this line:
Windows("BRNewBusiness" & Format(Date, "mm/dd/yy") & ".xls").Activate

Why is it doing this? What change do I need to make so the code will activate that already-open workbook?

david
--------------------------

Sub testAfterDAKCSupload()
'
Application.DisplayAlerts = False

MsgBox "new code follows...deskcheck before putting into production"
'edit the below remaining steps to get working properly

Workbooks.Open filename:= _
"D:B HNewBusinessReconciliationB H-MasterAccounts.xls"

'Sheets("summaries").Range("B7").PasteSpecial (xlValues)............

View 9 Replies View Related

Windows(" " ).Activate Is Not Working With Variable As Input

Feb 1, 2007

I have made a dialog box open using VBA to select some file,

Function FSel()
filetoopen = Application. GetOpenFilename("Document Files (*.xls), *.xls", 1)
Workbooks.Open Filename:=filetoopen
End Function

I need to activate the sheet which is selected in that string "filetoopen".

I am not getting the way to do this ,the normal Windows(" " ).Activate is not working with variable as input.

View 5 Replies View Related

Windows ("variable Filename").activate

May 26, 2009

I have created a macro to split out information from one excel workbook into 2 additional workbooks, and format them the way we need. Everything works fine on the filename I originally recorded the macro from, but is giving me a 'subscript out of range' error when trying to run the macro on a file with a different name.

currently, my test file has the 2 following fields that I somehow need to have as variables:

Windows("insmacrotest20090418.xls").Activate

and

Windows("Book1").Activate

I need the filenames and workbooks in the parenthesis to be variable so that the macro can be run on other reports with varying filenames.

View 10 Replies View Related

Run Workbook When Windows Start?

Nov 15, 2009

Is there any way of running particlular excel workbook when Windows start? Like when Windows XP or Vista start, excel workbook (Vehicle Maintenance) will run automatically? Not so sure if can use VBA or edit registry system which I would'nt like to touch at work.

View 2 Replies View Related

Delay In Switching Between Workbook Windows

Oct 22, 2009

Workbook Name Changes – But Workbook Does Not For Five Seconds?? MS Office Excel 2003 / SP3 Installed. I have noticed an odd occurrence from time to time while switching between workbooks. When I switch from one workbook to another, (typically via the “Window” pull down – but the same result happens when switching workbooks on the tabs at the bottom of the screen), the “switched to” workbook name will instantly appear at the top of the application, but the actual switch to the selected workbook is delayed for several seconds, (five seconds or so).

While not a major issue, it is annoying. I suspect that it is a memory allocation issue, (I have a Duo CPU System / XP Pro / 2 GB RAM), and with that in mind I have both restated MS Excel and also performed a complete re-boot. While either option does (seem) to result in the issue being (temporally) removed from my machine, it does return. I may go for several days without this issue occurring, and then it appears for no known reason

View 2 Replies View Related

Activate Different Workbook

Apr 23, 2008

I have two or more DIFFERENT INSTANCES of excel workbooks open at the same time. EX: Wk1, Wk2, & Wk3

Currently I have Wk3 showing in the screen.

Through a Macro, how can I display (bring to front) Wk1 without closing Wk3 ?

This is part of a longer macro, so I only need to know how to do above.

View 15 Replies View Related

Activate Another Workbook

Sep 16, 2009

I have an open workbook(A.xls) where the user can press a button which opens another workbook (B.xls) In workbook B they need to add new names then press another button to run another script. The script needs to switch to workbook A in order to work correctly. How can I switch to workbook A without using the name of the workbook? The reason I cant use the name to activate is because the first workbook that is open is not always A.xls

View 4 Replies View Related

Use VBA To Activate Sheet1 Of Workbook?

Oct 3, 2013

How do I use VBA to Activate Sheet1 of a workbook? I am using the following to go BACK a sheet, but really need to get to the first sheet in the workbook.

Code:
Sheets(Sheets.Count - 1).Select

If it's a new wb, then it's tab is called Sheet1.If it's an existing wb, and it only has 1 sheet, then I need that one (in case someone has added a sheet and deleted Sheet1)

View 3 Replies View Related

Activate Inactive Workbook

Aug 2, 2009

I am having a "cosmetic" issue that I was curious if I can fix it.. I have a workbook that opens a network workbook, saves some data to it, and then closes the network workbook.

Everything is working fine, except I cant get the code to "reactivate" the initial workbook. After excel saves and closes the 2nd workbook I opened, my screen stays on my desktop, instead of refocusing on the initial workbook. I have to manually click on the initial workbook in my taskbar to bring it back into focus...

I thought the below code would bring the inital workbook back into "focus" on the users screen, but it's not working.

Windows("NEWRightFit.xls").Activate
Sheets("Sheet1").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.ScreenUpdating = True
Windows("sxssubmit.xls").Activate
Confirmation.Show

End Sub

I just moved the Application.ScreenUpdating line as initially I had it at the very bottom, but that did not correct. The confirmation.show is just a dialog box that informs the user the changes were saved successfully. I would like the first workbook "sxssubmit.xls" to come back into focus automatically though, without the user having to manually select it from the taskbar.

What is my code missing?

View 9 Replies View Related

Activate Another Open Workbook

Aug 15, 2008

I have 2 workbooks open

Book1 has a call to ChangeBook()
Book2 is empty

Function ChangeBook()
Application.Workbooks("Book2.xls").Activate
End Function

ChangeBook() is located in a .xla file loaded on Excel launch.

Calling the function from Book1 does not cause Book2 to activate.

Running the function via the F5 key in the IDE works fine.

I am modifying existing routines that have problems and being able to activate certain books at will would make the changes much easier.

View 9 Replies View Related

How To Activate Workbook In Separate Instance

Apr 18, 2009

-I've been trying to get an answer to this problem for some time and I would like to try again from a different approach.

I may have 5 workbooks open in 5 different Instances of Excel. The number of files open varies. When the below Function is called, it checks to see if the file, trying to be opened, is already open or not.

Currently, if the file is already open, a mesage is displayed stating that it is open and the user has to search through tabs looking for the file so that it can be displayed.

What I want to do in place of a message, is activate the file that is already open and display it. No message necessary.

I have tried "Application.Activate" and can not make it work.

View 14 Replies View Related

Activate Sheet After Open Workbook..?

Jan 26, 2010

On a command button in a userform, I have this code to open another workbook:

View 2 Replies View Related

VBA To Activate Workbook With Reference To Name In Cell

Jul 9, 2012

i need a VBA code to activate workbook (which is already opened) with reference to name in cell A1

in Cell A1 is "masterworksheet.xls"

View 4 Replies View Related

Workbooks.Open Does Not Activate The Workbook

Nov 5, 2008

I have a workbook containing macros that opens a second one (no macros) and does some 'data mining'.

The following VBA is used for that (I use this code on dozen of files and it has always worked, except here):

Set WshShell = CreateObject("WScript.Shell")
ChDir (WshShell.SpecialFolders("MyDocuments"))
BladNaam = Application.GetOpenFilename("Excel File, *.xls", , "Excel")
Workbooks.Open FileName:=BladNaam
TabNaam = ActiveSheet.Name

The problem is that most of the time the newly opened workbook is NOT activated (i.e put on top) and thus the rest of the code is executed on the wrong workbook...

If I put in a Msgbox(ActiveWorkbook.Name) 9 out of 10 times the active workbook is the initial one and not the one that was opened by the code.

tried replacing Active.Workbook with wb (dim wb as workbook).
tried to wait-a-few-seconds in between code
tried renaming

View 9 Replies View Related

Activate Workbook Code Only Works On Some PCs

Oct 20, 2007

I wrote code to update some workbooks. The code opens the workbooks and then activates the workbook to add the update.

I was tired when I wrote the code to activate the workbook and it is written:

Workbooks("Update").Activate

The updates have already been sent out and it is not working on some computers. (If I change the code to

Workbooks("Update.xls").Activate

it works fine.)

Is there some option in the VBA editor that I can have people change on their computer so the code will run? Why does it work on some computers and not others?

(Unfortunately, rewriting the code to add ".xls" and resending everything isn't an option.)

View 7 Replies View Related

Workbook Activate Event Not Firing

Mar 19, 2008

button on main workbook opens 2 other workbooks and assigns a workbook object to them. the 2 opened workbooks are Activated in turn, range values changed and macros on these sheets invoked and results captured and pasted back onto the starter workbook. the macro is within a sub in a module as are the ones in the second workbook. An example of the code used is:

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

Dim wkbTest As Workbook
Set wkbTest = Workbooks("Test.xls")
wkbTest.Activate
Sheets("G").Activate
Range("Today").Value = Format(Now(), "dd-mmm-yy")
Application.Run "'" & wkbTest.Name & "'" & "!TestMacro" ............

View 5 Replies View Related

Excel 2013 :: Saving / Opening Workbook With Multiple Windows

Aug 7, 2013

With excel 2013 you're now able to open multiple windows or views of the same workbook. However when a workbook is saved with multiple windows open, the next user to open the spreadsheet will also open it with multiple windows. Which can be very annoying when most people work off of one window vs. multiple. Is there a way to disable saving the multiple windows or a macro to force open excel in 1 window?

View 2 Replies View Related

Ensure Active Workbook When File Opened By Windows Scheduler

Jul 23, 2009

This code opens a UserForm in the Workbook_Open event when the file is opened. After 10 seconds, the message is supposed to go away if the user doesn't click anything, then macros that follow are supposed to run. It is intended to allow a user to stop the macros. This works when you open the file manually (macro security must be low), you'll see a msgbox pop up after the UserForm goes away.

However, when the workbook is opened as a Windows Scheduled Task (Windows/ Control Panel/ Scheduled Tasks), the UserForm does not come to the front of the screen unless a user actually clicks on the window. It just flashes at the bottom indefinitely.

View 6 Replies View Related

VBA Code To Activate Second-to-last And Last Worksheet Tabs In A Workbook?

Dec 11, 2008

I use a workbook where new worksheet tabs are added and removed daily. Every day I use the second-to-last tab (2nd from the right) and the last tab (far right). What code would enable me to activate and reference both of these tabs individually? I believe it would be something like the code below but I can't figure it out...

View 5 Replies View Related

Excel Workbook Activate - Message Box Popup

Mar 19, 2012

I am writing code in Excel VBA and trying to control power-point. at the end there is message-box pop up. Everything runs fine.

But at the end I have go bottom windows bar and click on the excel file then message box comes up, otherwise excel tag keeps blinking at the bottom. I want to add something to code so message box in excel file is pop-up automatically without me clicking on excel file.

View 1 Replies View Related

Activate Open Workbook From Cell Reference

Aug 20, 2008

activate an open workbook based on the workbook name that is typed into a cell that is a named range?

So, I keep wanting to do this:
Windows.Activate Filename:=Range("MyRange")

Because this works:
Workbooks.Open Filename:=Range("MyRange")

View 9 Replies View Related

Worksheet Activate Not Happening When Workbook First Opens

May 18, 2009

I have a workbook where the sheets are all protected and I want to stop users scrolling up or across beyond the limits of the input areas.
I have used the following code (or variations of)...

Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:G32"
End Sub

This works fine except that when I first open the workbook, the first sheet displayed can still be scrolled. As soon as you move to another sheet and then back again, it is then OK.

It appears that the Worksheet_Activate code does not execute when the workbook is first opened.

How do I get round this?

View 9 Replies View Related

How Can I Activate An Open Workbook By Macro In Seperate Instances

Feb 24, 2009

I have a program that has all Excel Workbooks in seperate instances of Excel. There is a very sound reason for doing this.

The user has maybe 3 to 10 workbooks open. There are times when a workbook is active and has a Macro Link to open one of the already open workbooks.

When the user clicks the link, they naturally get an error message stating that the workbook is already open. Then they have to close the error msg and click on the Macrosoft Tab and look thru the list of open workbooks and then click the one they are looking for. Additionally, in this Menu Program the user really doesn't even have to know the name of the various workbooks.

I hope everyone will believe me when I state that this program works berautifully.

Right now I am simply cleaning up and making a few little things work better.

QUESTION When the user clicks on a macro link that is to open a workbook that is already open, how - On error - can I have the macro continue on and activate the requested workbook - - - Please remember they are all in separate instances of Excel.

Since the code I'm using can determine if the requested workbook is already open, I think there has to be a way to activate that workbook.

View 14 Replies View Related

Run Functions From Another Workbook

Aug 3, 2006

How can I execute a function that exists in workbook "A.xls" from VBA code in workbook "B.xls"?

Something like:
' -------- Code in workbook "B.xls"
Workbooks("A.xls")!My_Function(param 1, param 2)

View 3 Replies View Related

Copy A Workbook Functions From Addin

Mar 29, 2009

I have a addin and I ahve some code wriiten in the Workbook_BeforeClose event. On click some "Copy Sheets" menu option in my addin it creates a new workbook and copies some sheets from the addin to the new workbook.

But I want somehow have the code written in Workbook_BeforeClose event of my addin to be copied too...Since I am coping only the sheets , workbook events are not copied.

View 2 Replies View Related

Lookup Functions After Adding New Worksheet To Workbook

Dec 10, 2013

What I would like to do is have a master workbook that I can import different .csv files into as a new worksheet. Then calculations will be run on the values that are imported. My goal is to select a column and have corresponding list update the values. After that formulas will run on the calculations. I have got the import csv file down using VBA. The problem I was running into was with the Data Validation. Since I was overwriting my existing sheet I would get a #REF error because the link was broken. So I have worked my way around this for the list using the OFFSETSHEET Function:

VB:
Function SHEETOFFSET(offset, Ref)
[COLOR=#0000ff] ' Returns cell contents at Ref, in sheet offset[/COLOR]
Application.Volatile
With Application.Caller.Parent
SHEETOFFSET = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

This returns a value to a row in my mater sheet and I reference this for the data validation list.

However, I get the same problem when working with the HLookup function, the #REF error occurs. So far the only way I have figured out to work around this is to create another sheet that dynamically updates its values using the same OFFSETSHEET function, and my master sheet then references it.

View 8 Replies View Related

Functions Compared With VBA Functions

Mar 14, 2008

I am aware of the following topic in the VBA Help file:

"Using Microsoft Excel Worksheet Functions in Visual Basic
You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.

Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."

And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)

However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.

View 9 Replies View Related

Keep UserForm On Top Of All Other Windows?

Sep 9, 2013

I used to think my excel skills were OK until I started working with userforms but I now realise that I have a long way to go.

I need to enter data from various WEB sites onto a worksheet and at the moment I'm toggling between each web site and the worksheet. What I would like to do is set up a userform which I can enter data from each page into and when finished hit a commandbutton to transfer it to the worksheet.

I'm mostly OK with creating the userform but not sure what code, and where I need to add it, to keep the userform on top of all other windows.

Also not sure if it's possible, I have another worksheet that has a series of hyperlinks that when selected open a web page, I can create a combobox that shows the the display names of the hyperlinks but not sure what code I need to add a commandbutton or some other control that will open the hyperlink.

View 5 Replies View Related

Scrolling Of Several Windows

Nov 6, 2007

way of getting two sheets to scroll together, so when I scroll one window the other one moves too? Some text editors have that future (IIRC UltraEdit does)

I can't find a scroll event in the windows code to trigger a macro to run itself.

I can get around it by using a selection change event which would restrict me to moving around with the cursor keys/Pageup/down, but I've got 15,000 to review regularly in a short space of time and it would be quicker by mouse

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved