Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




Excel Application.workbooks.open(filename) Doesn't Return Excel Workbook


I use the following code to open work book. Then would like to reference the excel work book using excelwrk.

I can't reference excelwrk as excelapp.workbooks.open("c: est.xls") doesn't return any work book and Excelwrk is nothing.
I have no choice, have to reference worbook using excelapp.activework

Is there anything wrong with my code. Please advise. When I run application, there is no error. I am using Excel 8.0 object library.



Dim excelapp as Excel.Application
Dim excelwrk as Excel.Workbook
on error resume next

Set excelapp = GetObject(, "Excel.Application")
if err=429 then
Set excelapp = CreateObject("Excel.Application")
end if

Set excelwrk = excelapp.Workbooks.Open("c: est.xls")

Thanks

Yee




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Excel Application.workbooks.open(&"filename&") Doesn't Return Excel Workbook
Hi experts,

I use the following code to open work book. Then would like to reference the excel work book using excelwrk variable.

I can't reference excelwrk as excelapp.workbooks.open("c: est.xls") doesn't return any work book and Excelwrk is nothing.
I have no choice, have to reference worbook using excelapp.activework

Is there anything wrong with my code. Please advise. When I run application, there is no error. I am using Excel 8.0 object library.



Dim excelapp as Excel.Application
Dim excelwrk as Excel.Workbook
on error resume next

Set excelapp = GetObject(, "Excel.Application")
if err=429 then
Set excelapp = CreateObject("Excel.Application")
end if

Set excelwrk = excelapp.Workbooks.Open("c: est.xls")

Thanks

Yee

Can't Open Other Excel Workbooks While I'm Using A Workbook In My VB-application
Hi,

while I keep an excel workbook open in my VB application i can't open any other excelworkbook anymore outside of the application.

I use this code to open the workbook in my vb:

Set objWorkbook = objExcel.Workbooks.Open((sPrijslijstPath), , True, , , , True)

If i try to open any other workbook outside of my application (by clicking on a workbook in some folder), the 'shell' of excel opens, ...and in my taskbar a excel-icon appears.....but it doesn't show any content.

Probably this isn't a difficult question for all of you experts outthere?
What am i doing wrong?

Greetz,

Arthur

Open Workbook Automatically In A Different Excel Application
Hi guys!

just want to ask for your help.

Is there a way to open my excel file automatically in a new excel application. Even if there's an excel application already open. How can we do it in a macro code?

Thanks,
Dan

Move Several Workbooks Into One Workbook In Excel
Hello all,

Let me explain what I am doing in hopes you understand what I need. I am taking test data that is store by its serial number. It is store in three parts a sum file, dat file, and a csv file. For example 1251031.dat, 1251031.sum, 1251031.csv. Each file contains different information. I do not know even where to begin with this one.
I would like to automate the task of taking these three file and moving into one workbook, but I would like to select a folder containing about 100 or more file and have the macro find the files with the same serial number ignore the extensions and combine into one workbook.
I would like to be able to run this macro at the same time I run my other one that opens. edits, prints and closes each file. That way my changes made to each file can be save without change the filename. I will put my other macro in the attachment.

If you can help me please help.

Thanks for all your support and hard work.
RedDog

Merging Multiple Excel Workbooks Into One Workbook. #RESOLVED#
I am having a slight problem with Excel. I am trying to merge multiple workbooks (actually files containing a single workbook a peice).

I have got Excel to begrudgingly merge the workbooks using the copy method to an extent. It will work until hit the eleventh workbook/file and then gives me an "Automation Error" .

The way I have gotten this far is pushing the actual merge out to a VB Control and killing it each time a do a merge which is what MS tells you to do ("Excel will remain resident in memory until the reference to the object is destroyed. <-- ?It was design that way?") because it would error after merge one workbook/file.

Has anyone had any experience with anything remotely possible or would lead me to a possible answer to why eleven is the magic number I would greatly appreciate it.

I would even be open to an alternate to what I have been doing.

If you feel I need to explain a little clearer let me know.

Excel - Extracting Data From Multiple Workbooks For Insertion Into Summary Workbook
Hi all,
I am very new to VB and I’m hoping there are trivial answers to my questions. Any help would be greatly appreciated!

Here is what I need to accomplish:

I have hundreds of excel workbooks and I need to extract specific columns of information from them. Within each workbook there are 5 sheets. I only need to extract one column of information from 2 of these 5 sheets (B2:B8 from sheet “Header” and B2:B39 from sheet “LVB”). I need to import this data into a single sheet in a new workbook “Summary.xls”. Additionally, the column data needs to be transposed into a row. The first row will contain the data from “workbook1.xls” so that B2:B8 from “Header” will be followed by B2:B39 from “LVB”. The second row will contain the data from “workbook2.xls” in the same format and so on.

Can someone provide me with guidance or code on how to tackle this? I’m pretty sure I could figure it out if all this data was in one workbook, but different sheets. But I have no idea how to efficiently gather the data from the multitude of individual excel sheets.

Thank you very much for your time.

Setting An Excel Workbook Filename
Is there a way I can programatically change the name of an Excel Workbook without saving the workbook? So that the user can review the results and if desired just click save without having to specify a filename.

Thanks

Get The Full Filename (with Path And All) Of The Active Workbook In Excel
How can I get the full filename (with path and all) of the active workbook in Excel? Is there a function that returns it?
For example, ActiveWorkbook.Name would give me "Book1.xls", but I want a function that would return "C:Documents and SettingsMyUsernameMy DocumentsBook1.xls".

Saving Excel Workbook With New Filename Without Closing Original?
Without confusing you all with everything the workbook does I basically need to find a way in Excel VBA of saving the open workbook with a different name without using SaveAs as this closes the original workbook and I need to do this for 20 new filenames within the same macro.

The original workbook has lots of formulas and the macro deletes a lot of these before saving as a new file. I need to save the file before I remove all the formulas and then save and close the new file. After this the original workbook macro loops and repeats this process for 20 odd files.

If there is a way of opening a new workbook and copying everything from the first workbook (about 10 worksheets) across then I can do this as SaveAs will not close the original workbook.

Alternatively if there is a simple way of saving the current workbook with a new name without using SaveAs then this would be ideal.

Thanks

Richard



Edited by - rickp101 on 1/12/2004 8:36:35 AM

List Open Workbooks In Multiple Open Excel Applications
Hi,

I'am trying to get list of all open workbooks in multiple running excel applications in EXCEL VBA.

I'am trying a lot of solutions, but they only works with the active excel application.

Is there someone who can give me a fabulous solution ?

Troubles With Workbooks.Open In Excel VBA
Hi there!
I have a problem with the Workbooks.Open function.
I'm building up a tool with some Excel Sheet, each one to accomplish a specific task. To start them I made a main sheet (main.xls) with a UserForm as menu. Within main.xls VBA code there's a call such as Workbooks.Open("taskfile.xls"). taskfile.xls opens correctly. Opening, it should ask the user for a file containing data to import (datafile.xls) and then open it and read the data, but it fails in doing these two operations. By debugging taskfile.xls code I noticed that Workbooks.Open("datafile.xls") returns taskfile.xls again instead datafile.xls and that's why the Sub doesn't work, but I cannot understand why this happens! Is there anyone who can help me to fix it?
Thanks a lot!
Amarth

How To Know The Name Of All The Open Workbooks In All The Excel Sessions?
In order to know the name of the open workbooks, I have writen this code:

Code: Dim maVar as String
Code: For Each w In Workbooks
Code:     maVar= mavar & w.name
Code: next w

But there is an issue, indeed when I check maVar, I have only the name of the workbooks opened
wih the excel where this code is situated, thus i don't have all the names because often I have several
version of excel which are running in the same time.

I realy hope someone will have an answer!

VBA Excel - Open Multiple Workbooks In Sequence
I am trying to open many workbooks which do not share the same name due to the date refernced at the end of the name: "FileName_18112003.xls"
No need to mention I will not list them all in VBA. So I tried:

Workbooks.Open Filename:=FileName_*.xls"

which doesn't work !!
I actually also wants to open the workbooks in sequence, one by one, and retrieve data from these workbooks.

Thanks for your help!

Excel Macro's To Open, Print And Close Workbooks
I have three file type that I need to grab edit and print. These file types are .sum, .dat and .csv. The file names are like this 123.sum, 123.dat, 123.csv; 321.sum, 321.dat, 321.csv. Ok I already wrote to the point where I can open a range of files of certain type ex *.dat edit and print. But what I would like to do now is print files 123.sum then 123.dat, and 123.csv. Not all my Dat's first and csv and so on. I will attach my macro so you can have a better understanding on what is happen. It will be cut and pasted into notepad. If you can help Thanks
RedDog



Edited by - RedDog on 6/15/2002 12:55:54 PM

Excel Macro's To Open, Print And Close Workbooks
I have three file type that I need to grab edit and print. These file types are .sum, .dat and .csv. The file names are like this 123.sum, 123.dat, 123.csv; 321.sum, 321.dat, 321.csv. I have one hundred of each file type a day. Ok I already wrote to the point where I can open a range of files of certain type ex *.dat edit and print. But what I would like to do now is print files 123.sum then 123.dat, and 123.csv. Not all my Dat's first and csv and so on. I will attach my macro so you can have a better understanding on what is happen. It will be cut and pasted into notepad. If you can help Thanks
RedDog

I am sorry I uploaded the code that was wrong, not working.. Sorry!!
Thanks for your help...



Edited by - RedDog on 6/17/2002 4:15:58 PM

Excel Macro's To Open, Print And Close Workbooks
I have three file type that I need to grab edit and print. These file types are .sum, .dat and .csv. The file names are like this 123.sum, 123.dat, 123.csv; 321.sum, 321.dat, 321.csv. I have one hundred of each file type a day. Ok I already wrote to the point where I can open a range of files of certain type ex *.dat edit and print. But what I would like to do now is print files 123.sum then 123.dat, and 123.csv. Not all my Dat's first and csv and so on. I will attach my macro so you can have a better understanding on what is happen. It will be cut and pasted into notepad. If you can help Thanks
RedDog

Excel Macro To Return Filename, Size, And Date Modified From Folder
Hello,

I would like to obtain an excel macro that will return the filenames, size and date modified of the files from a folder in windows.

Thanks,
Rajesh

Workbooks.open(filename) Does Not Work In Internet Explorer...?
I have a program which users will run from a hyperlink, resulting in it being opened in Internet Explorer. Most of the coding seems to work fine, except the line

workbooks.open(filename)

where filename is a string variable containing a path to a workbook.

This works fine if you open the program up directly from its folder into excel, and also if you step through the code, but throws up Error 400 when it is run from a button.

Any suggestions on how to solve this?

Many Thanks

Andy

Excel Workbook Open
how do I call a procedure from workbook open...
Lets say I have a spreadsheet called "Interface" and a private sub called init_Me
On workbook open I want to run this procedure.
I have tried going into the workbook_open code and typing


Code:
Worksheets("Interface").init_Me
But this wont work.
I want this initialization procedure to run once at workbook open, but am having no luck at the moment.
Any ideas?

Excel Open Workbook
Hi, I have had a search around the forum and although there are various topics on workbook.open I cant seem to find out how to do what I want.

In my vba app I have a button called open, and when clicked I would like the file open dialog box to be displayed so that the user can select which xls file they want to open.

After this I want the excel file to be opened, data to be read from it which then populates a form before being closed. Preferrably I dont want the user to see that a workbook has been opened, i.e they will just get the data on the form, but this part is not essential.

I really just need to know how to open a workbook using the file open dialog box.

Thanks Ryan

How To Open A NEW Excel Workbook
I want to open a workbook where I can write some data and later on the user wants to save it or not, depends on the user. Similar to the manual opening of workbooks and then savin them..

I got this code working for an existing workbook. But I want to open a Book1.xls whereupon closing the prompt comes whether u want to save it or not. Just as saveas ..

Dim MyXL As New Excel.Application
MyXL.Workbooks.Open Filename:="D:My DocumentsMyWorkBook1.xls"
MyXL.Visible = True

Excel:get Open Workbook
Hi all
I try to make a code that checks if a user opens the right workbook. For example. I ask from a user to open the file book1.xls from a dir. I want to check if the user opened the right file. I try count and getobject methods without success.
Please help me!
Thanks

To Open Excel Workbook Using Vb
i need to open excel workbook using vb. the code to open the workbook is as follows:

Dim xlTmp As Excel.Application

Set xlTmp = New Excel.Application
xlTmp.Application.Windows.ResetPositionsSideBySide
xlTmp.Workbooks.Open "C: est.xls"
xlTmp.WindowState = xlMinimized
xlTmp.WindowState = xlMaximized

the above code open a workbook but in a wierd manner it does not display any toolbar not even standard toolbar. can some1 pls help how do i open the work in a normal display i mean with toolbars.

pls reply fast
thanks in advance

To Open Excel Workbook Using Vb
i need to open excel workbook using vb. the code to open the workbook is as follows:

Dim xlTmp As Excel.Application

Set xlTmp = New Excel.Application
xlTmp.Application.Windows.ResetPositionsSideBySide
xlTmp.Workbooks.Open "C: est.xls"
xlTmp.WindowState = xlMinimized
xlTmp.WindowState = xlMaximized

the above code open a workbook but in a wierd manner it does not display any toolbar not even standard toolbar. can some1 pls help how do i open the work in a normal display i mean with toolbars.

pls reply fast
thanks in advance

Return A List Of Open Workbooks
I am working with a program that spawns a new instance of Excel on the Windows XP mahcine, even if one is currently open. The file it spawns is "OWCSheet?????.XML" with the three standard sheets. The question marks are random digits and only the first sheet contains any data. I'm trying to write something that will read data from this spawned workbook into variables on my original workbook for additional calculations.

1) Is this possible?

1a) May I please get a nudge in the right direction?

To Open An Excel Application Thru Vb On A Machine Where Excel Is Not Installed.
Hi friends,

I dont know whether this question shud b posted here or in VB Newbies.
If not here then accept my apologies for same.

I have written a code (submitted here) which opens an excel application using VB 6.the code is given below...
Code:Dim xlapp As Excel.Application
Private Sub Form_Load()
'reference is made to ms excel 9 object
Set xlapp = New Excel.Application
Set xlapp = GetObject("", "excel.application")

xlapp.Application.Workbooks.Open App.Path & "G0060040005.xls"
xlapp.Application.Visible = True
    
End Sub


I created the package n installed it on various machines where Ms Excel is available..Its working properly there.
I have ensured tht file named 'G0060040005.xls' is present at app.path during run time.
the Support folder of this Package contains Excel9.olb file.
My problem is that I want it to work on those machines where ms Office is not intalled.
When i tried to run this code on those m/cs I received the following message
err.no= 429
Activex component cannot create the object.


First of all I wud like to know whether its possible to create excel object thru VB on m/cs where ms Office is not intalled.If yes plz let me know how?

Thanks in advance,
Jaishree

Excel VBA - Detect If Workbook Is Open
Is there a way (using excel VBA) to detect if a certain workbook is open or not?

I have some code that automatically opens a workbook and calls a sub in the newly opened workbook to extract some data. Then it opens another workbook and does the same. At the end of the sub called from the second workbook, I would like it to automatically detect if the first workbook is still open and, if so, close it.

How do you detect if a certain workbook is open within excel?

Any help would be greatly appreciated.

--Sean

P.S. I have taken the plunge! I start MCSD .Net training next month. So, you will probably be seing a lot more of me in different parts of the forum soon! I love this place. Everyone seems so friendly and willing to help. Unlike some other places where they get some kind of attitude if you ask a question that makes you sound like a n00b. Thanks to all for making this a great place to "hang". Now if I could only get the chat to work for me...

Excel VBA Macro On Workbook Open
I noticed that whenever using the Workbook_Open in "ThisWorkbook" code, it seems to delay the opening by a second or two, as though it is busy. Is there a way to speed this up? (i.e., it doesnt seem to matter what code is running, but the more code - the longer it delays).

Im looking for one of the solutions,

1) displaying a "Loading..." message (i tried with a form, but the form doesnt load until after the delay, which seems odd to me...).

2) simply finding a way to get rid of this delay

3) is there a Workbook_AfterOpen, or something?

Check For Open Excel Workbook From VB
I am in a VB program and what to close an excel app if it is running, so I know I need to first check to see if it is open and then close it.


Code:
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWS As Excel.Worksheet

' Some sort of GetObject here ??

' check for open Workbook here ??? How??
If XlWB is open Then ' real code not psudeo code here, help!
' Something like this??
Call xlApp.Quit
End If

Workbook Open Up On Excel Startup
Hi guys, this isn't strictly a VB question, but it is related. I'm looking for a way to get a workbook to automatically open when it is not in the standard excel start up folder.

The problem is that the Tech Support at my company have restricted access to C: to administrators, which means that I can't store any files here. Is it possible to change the file that contains workbooks that automatically open, and if so how?

If not, any other ideas on how i could do this would be wuch appreciated.

Cheers,
Dangleberry

Open 3rd Worksheet In Excel Workbook
Greetings,

My code is succesfully opening an Excel file, whose file name is stored in the variable "filename". From there, I need to access the 3rd worksheet only of this Excel sheet. Any idea how I would specify that in my code. At this point I have the filename, and I have the file opened. I just need to now specify that I want to read from the third sheet only.


VB Code:
Open filename For Input As #1Do While Not EOF(1)     Line Input #1, tmp     MsgBox tmpLoopClose #1


Any help would be appreciated.

Thank you,
Jim

Microsoft Excel Workbook.Open In XP
I am trying to open an Excel Spreadsheet in Office 2002 and on Windows XP. I open the spreadsheet with this code:

Dim App as Excel.Application
Dim Work as Excel.WorkBook

Set App = new Excel.Application
Set Work = App.Workbooks.Open("MyFile.xls")

This code works on Office 97 and on NT. Anyone know how I can fix this problem? Thanks for any help. Bret ps I tried referencing the old Excel library Version 9.0 and I tried it with the new 10.0 but both don't allow the open method.

Open EXCEL 2003 Workbook From VB 6
Hi, all,

I have a VB 6 application that will open an EXCEL 2003 workbook. The
problem I am facing now is like this. If there is already a workbook
open, it will try to close it first before opening the new one.
Although it prompts the user whether he wants to save the open workbook,
when the user chooses to
save, it does not really save but just closes it. The request is to keep
the open workbook still open, while opening another workbook. Any idea
what settings or somewhere something I can do to resolve this problem?

Thanks a lot!

Jonathan

Copy A Open Excel Workbook
Hi Forum

I have a macro which starts when a workbook open (I used the sub Auto_Open() ).

Every time the workbook open I want to make a backup of this workbook.

I used FileCopy but it doesn't work when the workbook is open. Is there a other command?


Any Idea?

macloy

Accessing Open Excel Workbook...
Here's my situation:
I have an Excel workbook open (from a fixed filename).
I would like to be able to access that workbook from a VB project external to Excel.
There could be any number of Excel instances/workbooks open at the same time - how can I get hold of the workbook that contains the calculations I need to access?
Note that I cannot open another instance of the workbook (for various reasons - not least that it takes over a minute to open!).

I encountered a workaround that created a new Worksheet which would get created in an open instance of Excel, getting the application by its property name. I can't find how to do this in VB, though, since I keep getting automation exceptions. Grr.

Does anyone know how I can get access to the list of open workbooks in Excel from outside the application? Or even the list of open Excel applications?

Failing that, does anyone know how I can write an Excel VB module that contains a timer? There seems to be no such functionality in VBA!

Thanks in advance.

Detecting Filename Of Open Excel Sheet
does anyone know if it's possible to detect an open Excel file within a VB program (not VBA) and and return it's filename/Work sheet name?

Open A New Excel Workbook - 'does Not Exist' Error
I'm using the folloiwng VB code to create a brand spankin' new workbook, but it give me a runtime 1004 error - 'the file could not be located'. That is true, as I want to create a new file.

What am I doing wrong? I just want to open a new file and work with it:

Dim xlExportApp As Excel.Application
Dim xlExportFile As Excel.Workbook
Dim xlExportWorksheet As Excel.Worksheet

Dim sExportFileName As String

Set xlExportApp = CreateObject("Excel.Application")

Set xlExportFile = xlExportApp.Workbooks.Open(sExportFileName)
Set xlExportWorksheet = xlExportFile.Worksheets(1)

xlExportWorksheet.Cells(1, 1).Value = "TEST"
xlExportFile.Save
xlExportFile.Close
xlExportApp.Quit

I'm sure this will be a no-brainer to experienced types, thanks for the help!

ab

Open Password Protected Excel Workbook
I'm currently using GetObject to access an existing workbook from a VB app. I modify and save the workbook without the user ever seeing it. I need to password protect the workbook, but can't figure out how to access it once it's protected. I do not want the user prompted for a password. Any help will be appreciated.

Import Excel Workbook Into An Open Worksheet
Dear people,

I'm trying to explain what must happened. I have an open spreadsheet(workbook), i need some values from another spreadsheet. Earlier i had implement a script to import a textfile, but now i must import a spreadsheet. The issue is that i must set the values into the correct cells. For examle: I need a button to import the spreadsheet. In this "closed" spreadsheet, i must take the range from c6 till the end and want to import this values into my "open" workbook from cell c6 till the end. I hope this is possible, could somebody help me with this problem. This is the code to import a text file into a worksheet, but now i must import values from an excel workbook into my worksheet. When we import this must not count but import the values into 1(name = worksheet1) worksheet, i hope you can understand my situation.

Sub ImportTextfile()



Dim wbk As Workbook, sFileName, wks As Worksheet
Set wbk = ActiveWorkbook
Set wks = ActiveSheet
Workbooks.OpenText _
Filename:=wbk.Path & " extfile.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
COMMA:=False, _
Space:=False, _
Other:=True, _
OtherChar:="|", _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2))

ActiveSheet.Move _
After:=wbk.Sheets(wbk.Sheets.Count)

Set wksValidate = ActiveSheet
wks.Activate
Set wks = Nothing
Set wbk = Nothing
End Sub



Greetings,

Furkan

How To Open Excel Workbook As Read Only Mode
hello


please kindly help me that how can i open worksheet in read-only mode

i am using the code like this,

how can i modify it


Code:
Dim xlWB1, xlWB2 As Excel.Workbook
Set xlApp = New Excel.Application
xlApp.Visible = False

Set xlWB2 = xlApp.Workbooks.Open(CurDir & "" & "KSEINDEX.xls")



thanks

Open An Excel Workbook From Visual Basic
Hi all,

How can I open an excel workbook from visual basic 6.0?

Regards,
Anirban

Check If Excel Workbook Open Latebinding
edit (Y use vb.net 2005)

Y want to check if a certain xlworkbook is open
and if so close it

I'am looking for code that uses latebinding
Y understand the concept about latebinding but y do not now what to code
y understand y don't need to set a reference, so y won't need these lines:
- Imports Excel = Microsoft.Office.Interop.Excel -
- Imports System.Runtime.InteropServices -

sofar y have only this
and only the GetObject seems to work
but how can y loop the workbooks and check for the name
y can't seem to set a variable like xlWB because it's not recognized
HELP,what am y missing to get it to work??????????
as y understood from the numerous posts, this works better then earlybinding
because you don't get version problems

Code:
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim xlApp As Object
On Error GoTo MyError
On Error Resume Next
xlApp = GetObject(, "Excel.Application")
If TypeName(xlApp) = "Nothing" Then.....



Thanks,
Richard

VBA Open Form When Excel Workbook Opened
Anyone know how to trigger a form/macro to automatically load when you open an Excel sheet?

I hate VBA... but it's all I have ATM.

Checking For Open Excel Workbook (RESOLVED)
Hello everybody

I`ve been using the Excel.Application, Excel.Workbook objects to open an Excel worksheet and transfer some data to it. The fact is that I want it closed when the user closes the form it was spawned from. I use the code below :


VB Code:
oWB.Close FalseSet oWB = NothingoApp.QuitSet oWB = Nothing


One problem with this code is that a user may decide to close the Excel workbook before he closes the form. In that case the code shown above crashes because the workbook has already been closed. How can I check to see whether the workbook is still open before trying to close it?

Import Excel Workbook Into An Open Worksheet
Dear people,

I'm trying to explain what must happened. I have an open spreadsheet(workbook), i need some values from another spreadsheet. Earlier i had implement a script to import a textfile, but now i must import a spreadsheet. The issue is that i must set the values into the correct cells. For examle: I need a button to import the spreadsheet. In this "closed" spreadsheet, i must take the range from c6 till the end and want to import this values into my "open" workbook from cell c6 till the end. I hope this is possible, could somebody help me with this problem. This is the code to import a text file into a worksheet, but now i must import values from an excel workbook into my worksheet. When we import this must not count but import the values into 1(name = worksheet1) worksheet, i hope you can understand my situation.

Sub ImportTextfile()



Dim wbk As Workbook, sFileName, wks As Worksheet
Set wbk = ActiveWorkbook
Set wks = ActiveSheet
Workbooks.OpenText _
Filename:=wbk.Path & " extfile.txt", _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
COMMA:=False, _
Space:=False, _
Other:=True, _
OtherChar:="|", _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 2))

ActiveSheet.Move _
After:=wbk.Sheets(wbk.Sheets.Count)

Set wksValidate = ActiveSheet
wks.Activate
Set wks = Nothing
Set wbk = Nothing
End Sub



Greetings,

Furkan

Opening Workbook In Existing Excel Application
I am trying to write some code which will open a workbook into an already opened instance of an excel application. The only code i could find on the internet would create a new excel application for each workbook you would like to open.

I would like to then automatically run code which is within the excel workbook i want to open (the code will use workbooks already opened which is why i need everything open in the one excel application)

It may involve a system call to return an object of the current opened excel application or perhaps it is simplier than this... Can anyone help?

Excel - Copy Info From Workbook When It Isnt Open
Hi all,
Im very new to VB and am just trying to teach myself the basics.

Ive written a simple piece of code for a quote sheet. When the Quote workbook is opened the code runs and uses a Vlookup function to locate pricing data in a cell on a seperate 'Pricing' workbook. The code works well if the 'Pricing' workbook is open, however if it isnt the code brings up an 'Update Values' window so i then have to locate the pricing workbook on my computer and direct the code to it in order for it to work. Is there a way i can program the code to automatically look in a specific location and not ask for user input?

Im sorry if im not explaining this well. If you need me to elabourate please ask. Id appreciate any input you can offer.

Code:

Private Sub Workbook_Open()

Dim ask As Integer
ask = MsgBox("Retail?", vbYesNo, "Retail or Distributor Quotes?")

If ask = 6 Then
Range("H25").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-15]C[-4],'[Pricing.xls]Sheet1'!R3C2:R135C12,8,FALSE)"
MsgBox "All sheets will quote Retail prices", vbOKOnly, "Retail Prices"
Else
Range("H25").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R[-15]C[-4],'[Pricing.xls]Sheet1'!R3C2:R135C12,9,FALSE)"
MsgBox "All sheets will quote Distributor prices", vbOKOnly, "Distributor Prices"
End If
End Sub

Workbook.Open Filename:=FileIn
I'm having problems opening a workbook using the VBA 'Workbook.Open Filename:=FileIn' statement... here's my code:
--------------------------------------------------------------

Public Function FileOpen(InputFileName)

Dim FileIn As String
Dim Wbk1 As Workbook

FileIn = InputFileName

' checking to see if workbook is open already. If not - open it!
On Error Resume Next
Wbk1 = Workbooks(FileIn)
If Err <> 0 Then
Call Workbooks.Open(Filename:=FileIn)
MsgBox ("Workbooks.Open has been called")
End If

' checking to see if workbook is open now
On Error Resume Next
Wbk1 = Workbooks(FileIn)
If Err <> 0 Then
MsgBox ("Workbook " & FileIn & " has not been not opened")
End
Else
MsgBox ("it's ok - " & FileIn & " has been opened")
End If

End Function

-----------------------------------------------------------------
Workbooks.Open does not seem to open the workbook and I don't know what to try next. I've even used the 'Dir' statement to return the workbook as a string and substituted the string into the Workbooks.Open command - and this didn't work either.

Also... I'm just starting to get to grips with VBA. I've got the 'MS Excel 2000 Power Programming with VBA' book - which is ok - but is not that useful as a quick VBA reference - any suggestions for a good desktop reference for VBA commands and flags would also be appreciated

Lots of thanks to the person(s) who can help me out with this - and to the web site owners for making it possible.......etc...etc

davenorm

Check Wether An Excel File Is Open Or Not And Search For A Value In That Workbook
Hallooo all
I have to open and search a value in an excel file from Access VBA searching alone is ok but if I run this programme again its opening one more copy even it is already opened. so please help me how to check and search in both cases ( I mean if it is not open we have to open and search other wise only activate that work book and Search for a value).

Please help me I tried for 6 days for this and I can't wait any more.
I appriciate any response.
Thanks a million in advance.

Kiran Karnati.

Copyright © 2005-08 www.BigResource.com, All rights reserved