Vlookup From Another Application

May 4, 2007

The problem is I need to do a vlookup from another Office application. I need VBAcode that:

opens up an excel spreadsheet,
performs the vlookup
and then retrieves the associated value.

View 2 Replies


ADVERTISEMENT

Application.VLookup In VBA

Jun 4, 2008

I've probably spent the last 4 hours reading posts from a bunch of different boards as well as this one, and I can't seem to pinpoint my problem. I'm trying to access data from a closed workbook via the following:

'Dim x As Variant

'x = Application.VLookup(Cells(1, 1).Value, Workbooks("C:Documents and SettingsmeMy DocumentsmyOptionsProgramsLiveUpdate.xls").Sheets("LIVE UPDATE").Range("$A$1:$C$5"), 3, False)

Cells(1, 2).Value = x

It doesn't seem to be working as I get the following error:

Run-time error -9

Subscript out of range

View 9 Replies View Related

Application.DisplayAlerts Freezes Application

Jul 14, 2006

Im copying and pasting data from one workbook to another but when I want to close the source workbook, it comes up with this message that I have much data and if I want to keep this in a clipboard. I thought I could disable this with Application.DisplayAlerts = False but when I do this, Excel freezes. Im I doing something wrong. How can I supress this window?

Public path As String
Sub Get_data()
path = "\Nlchoosa.nlOPS_Processes$OPS_ProcessesReports Sector performance"
Workbooks.Open Filename:=path & "ReportsSector Performance Reporting week.xls"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.copy
Windows("Sector Performance report Week.xls").Activate
ActiveSheet.Paste
Application.DisplayClipboardWindow = False
Windows("Sector Performance Reporting week.xls").Activate
Application.DisplayAlerts = False
End Sub

View 6 Replies View Related

Application Run

Mar 14, 2007

I want to make a macro where it will use the current filename of the workbook I have open (where is says New Quote Sheet 2.xls below) Is there something I can put where it will use the current filename when the filename is changed?

New Quote Sheet is a read-only template, and when a new quote is started, it is renamed.

Eg.
Application.Run "'New Quote Sheet2.xls'!Part8"
Sheets("8-Part").Select
Sheets("8-Part").Copy After:=Sheets(25)
Sheets("8-Pack").Select
Sheets("8-Pack").Copy After:=Sheets(26)

View 11 Replies View Related

Application.Run Error

Aug 5, 2009

I have the following

View 2 Replies View Related

Application.IgnoreRemoteRequests

Dec 11, 2006

With the help of Professional Excel Developement by Bullen Bovey and Green, I am building a Dictator Application. All-in-all, it is coming very well, EXCEPT that I can't seem to get the IgnoreRemoteRequests setting to get written to the registry properly on ShutDown.

On Open, I change this setting to True. In the BeforeClose event, I reset it to False, along with all of the Settings that I hosed on Open. All of the other settings get properly saved on exit, however, the IgnoreRemoteRequests is still set to True the next time Excel gets re-opened.

View 9 Replies View Related

VBA To Open Another Application

May 22, 2007

understand the procedure for opening another application from Excel using VBA?

First check if open, if so, activate, if not, open and set to active?

Any help would be great! -even links to other posts or otherwise

View 9 Replies View Related

Open New Application

May 29, 2009

I need a code to open a new Excel Application (Not just a workbook).

eg: say I already have book1.xls opened. Now I want a new excel application (say book2.xls) to be opened. Now, when I close any of the books (by using the cross on top right corner), the other shouldn't be closed.

View 27 Replies View Related

Usage In Application

Jun 26, 2009

done some VBScript spreadsheet reporting involving basically inserting data and some formatting.

I need some advice on what approach to take with Excel on my current project. The basic goal is to scrape project requirements off a word doc and load them into Quality Center (a test management tool from HP, henceforth referred to as QC). I have an hta/vbs process that offers the user choices gleaned from the QC API, scrapes the word doc, and creates a spreadsheet with a row for each requirement and a column for each property a "requirement" has in QC. These values are a combination of data from the word doc and values selected by the user in the preceeding hta interface.

The reason for this Excel step in the process is that all the values chosen up to this point are generic for the entire project. But there are a few properties that will be requirement specific. So all the info is collected in Excel and the user is to then go through each row and choose the "target cycle" (which I pulled off QC earlier in the process and display in Excel as a dropdown list). The "Platform Folder" and the "Module Folder".

Ok, so with that backround, here is my quandry... In the hta interface the user selected all the "Platform Folders" the project will have requirements in. These are then presented in a dropdown in the Excel, so the user can choose which of these Platform Folders this specific requirement should be loaded into. What I need to do is then have the "Module Folders" to be a dropdown that is dependant on the "Platform folder" chosen.

This is difficult enough for me, creating a dynamic dependant dropdown that will be replicated on each row. I don't know exactly how to do this but feel I can probably figure it out with all the help offered on these MVP sites.

What I can't figure out is where to do this. Should I set up the lists, formulas, and control code dynamically? Can these dependant list derive their values from comma delimited lists (like I did the non-dependent lists) or must they be in a worksheet? The only way I see to make the dependent dropdown work correctly is to have it run off a worksheet event. Can worksheet event code be applied dynamically or do I need to have a "template" type workbook containing the code in the application's folder and use that instead of creating a new workbook at exe time? I still have to write the upload into QC code, and that will probably be a macro that the user will have to install into their personal.xls. Can the dependent dropdown code be installed in the same way even though it needs to be event driven? Another aspect is getting the data for the dropdown. I can either get the lists earlier in the process when I already have a connection to QC and pass it along, or I can connect to QC from Excel and get the values dynamically. I am assuming passing them in will be more efficient.

View 9 Replies View Related

Database Application

Feb 5, 2010

I am very new with Excel and VBA, what i need to do is:
develop an initial log in to open an excel workbook...
and then open the workbook if username and password

1. ask for user name (field should be 50 characters)

2. Ask for a password (password alphanumeric with special characters field 25 characters)

3.Validate user to a salesman data base
if log in attempt fails after 3 attempts, purge buffer, clear log in screen and lock keyboard for 60 seconds)

I have done the whole excel application all i need is this thing to pop up when double click in my direct access to it..

Also save document with the invoice number that in in a cell

and How can I make a cell auto increase the invoice number every time open workbook?

View 9 Replies View Related

Using Application Events With An Add-In

May 23, 2006

I've created an addin to reformat spreadsheets that I receive in a particular format. What I would like to do is enable events so that whenever a spreadsheet is opened the reformatting procedure is run (this also validates whether the spreadsheet is of the correct format).

I have created a class module with the following code (exactly as the Excel help):

Public WithEvents App As Application

Dim X As New EventClassModule

Sub InitializeApp()
Set X.App = Application
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
SortE1Output 'This is my procedure that determines whether the
'spreadsheet is of the correct format and then reformats it
End Sub

"After you run the InitializeApp procedure, the App object in the class module points to the Microsoft Excel Application object, and the event procedures in the class module will run when the events occur."

When I try to make a call to the InitializeApp procedure in the auto_open procedure (in a different non-class module) I get a "compile error: sub or function not defined".

View 6 Replies View Related

Hide A Application

Oct 20, 2006

If you run a macro like this: Application.Visible = False. How do you get back to the application

View 3 Replies View Related

Minimize Application

Feb 13, 2007

i want to minimize the excel application and show a userform on startup.

View 2 Replies View Related

Start A New Application

Mar 22, 2007

I have an excel file which automatically loads an user form once the file is opened and when the user form is closed, the file also closes. When the file is open i am not able to switch to anyother excel file because this userform is active. Is there a way, when i open the file with userform, a New excel application starts. So that i can use the other excel files opened by the previous excel application.

View 4 Replies View Related

Application.filesearch

Jun 27, 2007

I have created a macro that must extract data from other workbook. It opens the workbook, with an exist, and isopened control, and then open it. Itīs running well, but i found while making another macro, that if the file direction is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", then i cant run the exist control. Next you have the code im using. In the first version, i had .LookIn = ThisWorkbook.Path. Now Iīve tried:

1. Dont put .LookIn, and fName is "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls"
2. .LookIn = "http://www.xxx.com:8080/"
.FileName = "Challange%20Cost%20Proposal%20Sheet.xls"

Both are failing, it says, the file doesnt exist. If i try directly WorkBooks.Open "http://www.xxx.com:8080/Challange%20Cost%20Proposal%20Sheet.xls", it opens correctly, but i would have a control, because the original file name could change, and the macro would crash if does.

Function OpenWorkBook(fName) As Integer
If Exists(fName) Then
If (IsOpen(fName) = False) Then
Workbooks.Open (fName)
OpenWorkBook = True
Else
MsgBox "The WorkBook " & originWorkBookName & " is already open."
OpenWorkBook = -1
End If
Else.........................

View 5 Replies View Related

Mouse Over In Other Non VBA Application

Mar 15, 2008

Is it possible to get mouseover information from outside of Excel using VBA? I'm trying to automate a non-microsoft package but I need my mouseclicks to wait for a message to appear within the other application before my macro continues. I've already asked something similar before but I've put a better title on this thread so apologies for the repeat.

View 2 Replies View Related

Controlling Another Application Where Hotkey Is Not Available?

Jan 29, 2014

I am working on an Excel macro which uses the Shell function to open another application, the AppActivate statement to change focus to that application, and then a series of SendKeys statements to perform tasks for which keystrokes (hotkeys) are available. However, there is one step in the process which does not have a hotkey available, but requires clicking on a drop-down with the mouse. Is there a way to have an Excel macro "click" on something for which a hotkey is not available?

View 1 Replies View Related

Macro To Quit Application

Jun 16, 2014

I am trying to add the following to my macro to close excel when the macro is completed:

[Code] ......

This will save the workbook before exiting. How do I direct it to not save any open workbooks and exit the app?

View 1 Replies View Related

Open An Spreadsheet Except Through The Application

Dec 16, 2008

I cannot open an Excel spreadsheet except through the Excel application. This includes through Windows Explorer, on the internet (this forum), in email. I need to save the file first, then open Excel, then open (through Excel) the file.

In Email, I get "System cannnot find the file specified" On this site, I get "Access to the specified device/path is denied" From Explorer, "Cannot find the file.... based on the criteria xls(or one of its components). Make sure the path and file name are correct and that all required libraries are available."

Last time, our IT group couldn't fix it and had to reinstall the entire Office package to get it working again. I'd rather not go through that.

View 2 Replies View Related

Close And Save Application

Dec 5, 2008

When a user X's (clicks of the X in the titlebar) of a user form, I want to save whatever input has been made to that point and close the application. this is what I tried but it neither saved input nor closed the application

View 2 Replies View Related

Macro Range Application

Jan 14, 2009

I have a worksheet using several columns and 100 rows. Column B and column C can contain a value "x" in either coulmn but not both in a specific row. The macro illustrated works perfectly for what I need but there must be a way to point the macro to a range of rows rather than using an "if" statement for each row as I've done.

View 5 Replies View Related

Application.Input Box Validation

Jul 9, 2009

I'm trying to get input from a user regarding parameters for loan calculations. I'm still very new at VBA so some of this will seem very silly, but I've been trying for hours to get this to work right and I keep fixing problems and creating more simultaneously. I've come to the conclusion that I have a fundamental misunderstanding of how this works.

This one actually works fine, but I am including it just in case I've done something less than perfect and someone can correct it.

View 9 Replies View Related

Creating A New Application Through Macro

Nov 8, 2009

I want to create a new excel application through macro. I basically want to transfer few columns from the parent workbook to new workbook. but these new workbook must be contained in a new excel application.

View 2 Replies View Related

Unhide A Hidden Application

Jan 4, 2010

Already read some threads about how to hide and unhide an excel application.
I was able to hide my excel application, but, somehow I'm not ale to unhide it when I close my Form:

View 4 Replies View Related

Intersect 2 Ranges In One Application

Jan 11, 2010

When trying to intersect ot ranges in an application it selects all ranges example:

View 2 Replies View Related

Application.DisplayAlerts Not Working

Jan 14, 2010

I am trying to suppress the message box displayed while executing the macro.

View 2 Replies View Related

Application.Filesearch 2007

Feb 5, 2010

Filesearch......

Just been upgraded to 2007 and computer says no......

Iv only been VBA'ing for around 6 months and really am still very new to it.....

The bit that goes wrong is:

View 11 Replies View Related

How To Check Which Application Has Focus

Apr 10, 2012

How to change focus back to Excel from other applications, how simply to report what application currently has keyboard focus - there is no need to change it?

View 2 Replies View Related

Closing Another Application From Excel VBA

Sep 19, 2013

I have code that opens a .pdf file and copies the content to an excel spreadsheet.

However, in order to loop through multiple pdf's, I need to be able to close the Adobe Reader application before I open each new pdf file.

With excel being the active application, any suitable code to activate and close Adobe Reader?

View 1 Replies View Related

Hide Application Main Bar

May 27, 2006

how to hide the main bar from Excel? the one where says "Microsoft Excel - "Name of the workbook"

View 9 Replies View Related







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