Visual Basic :: Excel - Workbook Edit

Macro To Edit Macros In Another Open Workbook
Please help me!

I have a workbook that contains macros. There is a spelling mistake in one of the Macros, that I need to correct. However, there are 300 copies of the file being used by remote users.

I want to create a macro in a "fix spreadsheet" that I can email to the users, so they can activate the macro and it will go into their faulty spreadsheet and fix the spelling mistake. Can anyone tell me how to do this?

Open Excelworkbook, Edit Sheet, And Save Workbook
Hi guys,

Till now I have found all answers concerning vb 6.0 @ xtremevbtalk.com, but not concerning Excel automation ; although: not what I wanted. Everywhere man can read how to save data to a excelsheet, but I've never read how to load a specific file and to modify it, and save it. So this is what I made:

Code:
Set oExcel = New Excel.Application
oExcel.Visible = False
oExcel.Workbooks.Open FILE
oExcel.Application.AutoRecover.Enabled = True 'Alternative

Dim oRng1 As Excel.Range

Set oWB = oExcel.Workbooks.Add(FILE)
Set oWS = oExcel.ActiveSheet

Set oRng1 = oWS.Range("A2")

oRng1.Value = "He Lives!"
' OR:
X = oWS.Range("A2").Value
oWB.SaveAs FILE & "i"

On Error Resume Next
Cleanup:
Set oWS = Nothing
If Not oWB Is Nothing Then oWB.Close
Set oWB = Nothing
oExcel.Quit
Set oExcel = Nothing
Kill FILE
Name FILE & "i" As FILE
I hope this will be usefull. Maybe you could improve the code? Especially the last part: I don't want excel to prompt me about whether it may overwrite the existing file or not... therefore I wrote that peace of code.

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

Reading Values From A Closed Excel Workbook (Excel 97) **RESOLVED**
Morning all,
This is further to an old post.

I am looking to read data values from a closed file and write them to an open workbook. I am hoping to do this not simply value by value but using large ranges.

e.g. On sheet1 of the closed file I need to read values for the range("A1:F100"). e.g. if the closed file were open:

Code: Workbooks(OpenFile).Sheets(Sheet1).Range(A1:F100).Value=Workbooks(ClosedFile).Sheets(Sheet1).Range(A1:F100).Value

a) Does anyone know the code to read values from a closed file (even if value by value)?
b) Is it possible to make this method read large data ranges?

Any help would be great.
Cheers,
M_B

Edited by - M_B on 5/28/2004 4:09:44 AM

Copy Excel Data To New Excel Workbook HELP!
I have a excel workbook with thousands of peoples names and info. A persons record can be in the workbook more than once. I want to create a macro or somthing in excel to copy all of the same people with the last name Smith for example to a new workbook. How do I go about doing this, anyone with example code or help.

Thanks

Non-Termination Of Excel.exe Even After Closing The Excel Workbook Through VB
Hi
I have a VB code that executes every one minute and checks for a time match. If the time matches then it has to execute a code in which i open several workbooks one by one fill in some details and then close the workbook. Since it is a continuously running program i cannot close the form and the excel.exe is not getting terminated in the task manager. As a result Excel is getting hanged and i am unable to open any Excel file by double clicking in the explorer(May open once).

This is my code

Private Sub Timer1_Timer()
If Format(Now, "hh:mm") = "12:00" Then
call_func = open_excel()
End Sub

Public Function open_excel()
Excel.Workbooks.Open "D:Tables emplate.xls"
' My code
Workbooks.Close
End Function

This code should be run and the form should not be closed. I tried to terminate the Excel.exe process by getting the handle of window
Used this code for the function

Public Function open_excel()
Excel.Workbooks.Open "D:Process Termination emplate.xls"
' My code
Workbooks.Close
Dim lHwnd As Long
lHwnd = FindWindow("XLMAIN", vbNullString)
ProcessTerminate , lHwnd
End Function

If i terminate the process it is not able to open excel the next time through code and the code is giving error. Can anyone help me out. Thanks in advance.
Sameera

Fill A Vba Form With Excel Workbook Data, Updating The Same Workbook W/ Data From The Form
Okay I have a basic to intermediate understanding on using vba and Excel utilities. I need help with the workflow of my application rather than specific procedures.
I've got data on only one worksheet where each record is defined by an account id. Every cell on each record is populating a specific textbox on a form. The form is a basic interface for users, moving from one record to next. So mostly the data will flow from the worksheet to the form with the exception of one field for each record. The user will update that field as needed on the form. There are only 30 records or so on the worksheet. I can't rationalize learning Access and/or some database connection since there are only a few records and few fields.

This is an example of the field headers and one record:
Account Id, Account Name, Total Balance, Past Due Balance, Status
011,ChevyChaseAccount,$1200,$100,Customer sent in check (not posted).

I can think of only one way to update the form with the data and that is piece by piece:
Code:Private Sub UserForm_Initialize()
    With frmDetail
        '.txtAccountName = ActiveSheet.ActiveCell.Offset(0, 2).Value (this gives me an error since I can't use the offset property for the code in forms)
        .txtAccountName = ActiveSheet.Range("B2").Value (since I can't use offset then I have to resort in a specific address)
        .txtAccountId = ActiveSheet.Range("C2").Value (since I can't use offset then I have to resort in a specific address)
        .txtTotalBalance = ActiveSheet.Range("D2").Value (since I can't use offset then I have to resort in a specific address)
    End With
End Sub

I haven't thought of a way to move from one record to another let alone how to update that one field to another.
Let me know if you have any questions,

Display Excel In IE And Edit And Save Excel At Server
I need to display an Excel sheet in IE . And Then i want to edit it in IE and save the changed excel sheet back to ther server.

I can view excel in IE by setting
Response.Contenttype="application/vnd.ms-excel"
.I can then edit excel but i don't know how to save changes to the server .
Please help

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

Excel Workbook W/ VB
Simple question and I am being to stupid to figure it out. I want to do something like this

Dim objExcelWkSheet as worksheet

I was doing some research and found that I can manipulate an excel worksheet in vb if I declared an object of type worksheet.

However I cannot get the option worksheet. I added into References "Microsoft Office 9.0 Object Library" yet it still errors out "user defined type not defined"

Can someone please clue me in on how I get worksheet as an option.

Thanks

Name Excel Workbook
quick question....
Is it possible to name a new excel application (workbook) when it's created using VB6? So instead of calling it by default "book1", "book2", etc. it's called with the name that I give it.

Add At The End Of Excel Workbook
Hello, Im trying to open an excel file and add data to it.

Code:
Dim excl As Object
Dim bk As Object
Dim sht As Object
Dim i%, j%

Set excl = CreateObject("Excel.Application")
Set bk = excl.Workbooks.Add(App.Path & "BLANK ARTIST SONGBOOK.xlsm")
Set sht = bk.ActiveSheet

For i = 1 To ListView1.ListItems.Count
sht.Cells(i + 2, "A") = ListView1.ListItems(i).Text
For j = 2 To ListView1.ColumnHeaders.Count
sht.Cells(i + 2, Chr(64 + j)) = ListView1.ListItems(i).SubItems(j - 1)
Next j
Next i
excl.Visible = True

But it over writes on the data that exsists on the workbook book. I want it to add the data to the bottom. Any idea? Thank you

Vba And An Excel Workbook Help Please
Hi what i want to achieve is to get all the worksheet names in this one workbook and list them in a drop down combo box. what i need to know is how do i get the name of every worksheet in this work book (worksheets are always being added so obviously needs to be dynamic to work correctly)
thanks
regards
Mark

Servers For War: Taking Gaming To Extremes

Excel Workbook
Hi, how to open excel workboook (the excel file resides in a web server eg http://portal/excel/ttt.xls) without alerting the challenge response?
I want to bypass the challenge response, how to do that?

eg
Set xlApp = CreateObject("Excel.Application")
Dim strDocPath As String
strDocPath = FilePath
strDocPath = strDocPath & "Templates/"
Set xlBook = xlApp.Workbooks.Open(strDocPath & "ttt.xls")

Excel New Workbook
I would like to open a new workbook in excel in my VB programme. I am currently able to open a already present workbook using the statement:
Xlapp.workbook.open (cdsave.filename) where cdsave is my dialogue box.

I would like to have the similiar ability to open a new workbook with a filename entered in my common dialogue box.
Is there a similiar method available?

Thanx a lot.

Excel - Unhide Workbook
Hi, I'm new to this forum, but I hope someone can help. I've got a VB application that opens two Excel spreadsheets and does some data manipulation and graphing. The problem is that when the application opens the second spreadsheet, it is hidden. I can manually go to the Excel "Window" drop-down menu and "Unhide" the worksheet, but I'd like to do this within the VB application. Or better yet, not have it hidden at all when I open it!

Any ideas?

Thanks in advance,
CP

Opening A New Workbook In Excel
OK, next question

If Excel is open with a workbook already loaded, how do I get it to create a new workbook? My code works fine if Excel is not running at all, or if it is running but does not have any workbooks open. If a workbook is open, it should create a new one, but it doesn't.

My code:

StoreDir = CurDir + ""
On Error Resume Next
Set objExcel = GetObject(, "Excel.application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear
DetectExcel
Set objExcel = GetObject(StoreDir + "LLINES.XLS")
objExcel.Parent.Windows(1).Visible = True

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?

Has Excel Workbook Changed?
Hi folks,

I have opened up an Excel workbook from VB (through objects not shell). Is there any property that I can check in VB that will tell me if there have been any changes made to my workbook?

N.B. I had to disable all the save buttons so cant check them

Deleting An Excel Workbook
In code. How can I do this?

Thanks!!

Creating An Excel Workbook
I'm working in VB and I want to check to see if a workbook exists, and if it doesnt, create it then open it to put info into. I know how to open it, I just dont know how to create one

Thanks!

Excel Workbook With No Sheets
I have a simple question about creating Workbooks in Visual Basic.

First of all is there any way to use the Excel.Application object to create a sheetless workbook? If not is there a way to delete sheets from the workbook without getting the message "You are about to delete a sheet...etc"

Dim excel_app As Excel.Application
Set excel_app = CreateObject("Excel.Application")
excel_app.Workbooks.Add

I sometimes only need the workbook to have one sheet and the previous code makes a workbook with 3 sheets. Even if I can make a 1 sheet Workbook, I can work around it.

P.S. the code i use to delete the sheets is this:

excel_app.Sheets("Sheet1").Delete ' These delete all 3 sheets but prompt the user asking them if they want to
excel_app.Sheets("Sheet2").Delete
excel_app.Sheets("Sheet3").Delete

Excel Workbook - ReadOnly - VBA
Hi All,

Is there anyway via VBA to work out whether a workbook is readonly?

Reason is i have some code that runs when the user closes the file yet when the workbook is readonly then it causes problems...

Basically I want to say is that if the workbook is readonly then don't save the current workbook.
This code currently sits in: Thisworbook

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Sheets("Scoreboard").Select
Sheets(GetUserName).Visible = False

ActiveWorkbook.Save
Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub
vaneagle

Excel VBA Allow Focus On Another Workbook
Another question...

I have a loop running, which updates a new value in Cell A1 on Workbook 1 every second. I also have Workbook 2 open.

While the loop is running I am unable to click on another workbook in the taskbar, it simply stays on Wookbook 1. However, if I minimize Workbook 1 (the loop will still run) I am able to select Workbook 2 just fine.

How can I fix this problem, so I can click on Workbook 2 in the taskbar, and it will bring focus to Workbook 2, instead of Workbook 1?

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

Activate Workbook Excel VBA
The following procedure works just fine until I get to the line to activate the workbook (This is just before End Sub). Can anyone tell me why this line does not work?

Code:
VBA:

Public Sub prac()
Dim swb As String
swb = Application.GetOpenFilename
workbooks.Open filename:=swb
ThisWorkbook.Activate
ThisWorkbook.Sheets(1).Range("A1:A5").Select
With Selection.Interior
.ColorIndex = 0
.Pattern = xlGray8
.PatternColorIndex = xlAutomatic
End With
workbooks(swb).Activate
End Sub

End VBA:

Searching An Excel Workbook...
Hi I am having more problems with the code that I have created, half of it works. I would like an input box to appear and then a reference number is inserted. So far I have the code below. When searching for a customer reference number that is in the quote history page the value is found and then copied to the relevant sheet. The problem which I have is that when the number is not in the quote history page then an error occurs in the code. When I click debug to go into the code the highlighted bit is "R.Select". The code is pasted below:

Code:
Public Sub RQ()

Dim ReferenceNumber As String, R As Range

ReferenceNumber = InputBox("Enter Reference Number?")
Sheets("Quote History").Select
If ReferenceNumber <> "" Then
Set R = Sheets("Quote History").Cells.Find(ReferenceNumber)
R.Select
Selection.EntireRow.Copy
Sheets("Data for Retrieve Quote").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Retrieve Quote").Select
Range("D2").Select
Else
MsgBox "Quote not found.", vbExclamation
End If
End Sub
Any ideas on how to make this work will be grateful. Thank you very much in advance...

Closing An Excel Workbook
I'm writing VB in an excel worksheet and would like to open then close another excel workbook with read permission only. I have no problem opening the file w/

Workbooks.Open "\MeltplantSolution IV6-11-04.xls"

but where can I control permissions?

I've experimented with WorkbooksObject.Open(), and simply can't get it or WorkbooksObject.Close() to work.

Your help is appreciated.

Using A Variable For Excel Workbook Name
I'm trying to copy information from several workbooks (using the variable wbname) into one workbook called Analysis.

My problem is that the program doesn't seem to recognise that wbname is a variable. When I run the program, it states that 'C:ALCPtOneButAnalysis wbname .xls' can't be found. As I've declared n = 1 and wbname = n, it should say that "1.xls" can't be found. I have checked that 1.xls does in fact exist in the appropriate folder.

The program doesn't get any further than here, so I'll only include the code up to here:

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim xlWBAnalysis As Excel.Workbook
Dim intSheet As Integer
Dim strSheet As Integer
Dim n As Integer
Dim wbname As String

'open excel application
Set xlApp = New Excel.Application
'Open excel workbook
Set xlWB = xlApp.Workbooks.Open("C:ALCPtOneButAnalysisAnalysis.xls")
x = xlApp.Range("A100") ' this is the number of tests to analyse
n = 1 'this is to increment the loop to the number of tests
s = n + 1 'this is to set the sheet number to paste into
wbname = n 'this is to define the name of the workbook to use

Do Until n = x

Set xlWBAnalysis = xlApp.Workbooks.Open("C:ALCPtOneButAnalysis wbname ")

How can I get the program to recognise the value of wbname?

Excel Workbook Size
I am new to VBA and was wondering what a normal size is for a workbook. I have a workbook with 6 worksheets, 5 userforms, and about, 18 procedures with about 220 lines of code.
About 5 lines containing data (I am not using the sheet yet, just developing it).
The file is already 3.9Mb

Is this normal?

Rob

Excel Workbook Object
So I'm trying to get the active sheet when a user clicks on a tab at the bottom of Excel. I have trapped the SheetActivate event and in the event code I'm trying to get the active sheet object. But for some reason it gives coming up with a null object whenever I try to get the Workbook.

Code:
Dim theWorkbook As Excel.Workbook
MsgBox mWorkbook.Name
theWorkbook = mWorkbook

When the msgbox pops up, it finds the right name of the open workbook. But when I try to assign the mWorkbook object to theWorkbook, I get the null object error. It's like the workbook object just suddenly disappears. Anyone know what's going on?

Thanks.

Excel Workbook Problem
If you guys could help me out it would be great! My problem is that i am using the below code to take data from a msflexgrid and convert it into an excel worksheet. The code works just fine for that but the problem is that if I close excel and then run the procedure again to transfer the data to excel it opens excel but not a worksheet. Excel is just blank and no worksheet comes up. If i don't close excel and run the code again it simply just opens a new workbook.

Here is the code

Code:
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook

Private Sub Convert2Excel()
Dim i As Integer
Dim j As Integer
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number Then
Err.Clear
Set objExcel = CreateObject("Excel.Application")
If Err.Number Then
MsgBox "Can't open Excel."
End If
End If
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add
AppActivate "Demo"
For i = 0 To grdInfo.Rows - 1
grdInfo.row = i
For j = 0 To grdInfo.Cols - 1
grdInfo.col = j
objWorkbook.ActiveSheet.Cells(i + 1, j + 1).Value = grdInfo.Text
Next
Next
End Sub

Can't Get Excel Workbook To Run Maximized
I'm trying to make Excel run maximized when the users open up the program. The reason it's not running maxmized is because it's ran from Internet Explorer on our Intranet. By default the window is in "restored" mode and even putting the code
Code:
Application.WindowState = xlMaximized
in doesn't do the trick. Any ideas on how to make this thing run maximized all the time?

Also worth noting is that sometimes the code does run and maximizes it and other times it doesn't. Not sure what I'm missing here.

Formating Excel Workbook From VB
Can someone please tell me why the following code does not resize the columns in my worksheet? This has drove me batty all night!

Thanks in advance,

Code:
Option Explicit

Private Sub cmdGen_Click()

Dim xlApp As Excel.Application
Dim wkbNewBook As Excel.Workbook
Dim wksNewSheet As Excel.Worksheet
Dim xlsfilename As String
Dim txtfilename As String

xlsfilename = App.Path & "Survey.xls"
txtfilename = App.Path & "Survey.txt"

Set xlApp = New Excel.Application
Set wkbNewBook = xlApp.Workbooks.Add
xlApp.Workbooks.OpenText FileName:=txtfilename, FieldInfo:=Array(1, 2)
Columns("A:A").ColumnWidth = 110 '**???**

'---- Save and Quit Excel ----
xlApp.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:=xlsfilename
xlApp.Quit
Set wkbNewBook = Nothing
Set xlApp = Nothing

Unload Me
End
End Sub

Excel Macro In Different Workbook
I'm writing my first program that uses Excel files and crap. In a workbook on our server is a file called "duplicates.xls" that contains a macro that I use a LOT in different workbooks. I've searched and searched but I can't seem to find a way to call that macro from that workbook, but have the macro perform it's actions on the workbook I already have open in my code.

It would normally be run by clicking cell "A2" in the workbook you're using, then clicking the little (Custom Macro Button Thing). duplicates.xls would open and run the macro on the workbook you've already been working with. How does one accomplish this via code?

Appreciate ALL help!!!!!

Excel Workbook Problem
I have a system that is being modded to run on a server as an executable. It has Office 2000 installed on it. Most of the code runs but snags on this point:

<vbcode>
objExcel.Workbooks(1).Sheets(1).Range("D" & srcRow & ":O" & srcRow + 9).Copy
</vbcode>

The error:
<error>
Application defined or object defined error
</error>

srcRow=0

Do I need a later install of Office? Or is the value of the variable incorrect in the application

Opening Excel Workbook With Different Name
Hi I need to re-use code as much as possible. I need to use the same procedure to open three different workbooks (one at a time) but it is not working.

This is what I have

Code:
Public Sub AddRow(RowData As String, NameFile As String)
Dim oXLApp As Excel.Application
Dim oXLBook As Excel.Workbook
Dim objExcelCI As Excel.Chart
Dim oXLsheet As Excel.Worksheet

Set oXLApp = New Excel.Application 'Create a new instance of Excel
Set oXLBook = oXLApp.Workbooks.Open("C:logNameFile.xls")
Set oXLsheet = oXLBook.Worksheets(1)

End Sub

How should I define NameFile (String?) so that I could use it in this line
Set oXLBook = oXLApp.Workbooks.Open("C:logNameFile.xls")
with one of three names I have.
How should I call this function from any other sub?

Thanks

Excel Workbook Load
Hey all

I did a search on this but found nothing apologies if its been covered.

Im using VB6, and loading an excel file ( but not displaying ) to gather some data. My problem is that when opening the workbook it takes forever to load. I pretty sure norton isnt causing the problem with virus scans either.

Code:
Public xlApp As New Excel.Application
Public xlbook As New Excel.Workbook
Public xlsheet As New Excel.Worksheet

Set xlbook = xlApp.Workbooks.Open(FileName) 'open the workbook
Set xlsheet = xlbook.Worksheets(Sheet)

Is the command Set xlbook = xlApp.Workbooks.Open(FileName) that takes forever to run, the filename is passed in from somewhere else. It doesnt matter how much stuff is on the excel sheet, just takes like 10 seconds to load it up. any ideas? Am I able to read from a user specified excel file without having to open the workbook? I may need to write back to the worksheet in future so i guess not...

Opening Excel Workbook Via VB
below is my code snippet:

VB Code:
Public Function Department_1()Set ExlObj = CreateObject("excel.application")app_path = App.PathIf Right$(app_path, 1) <> "" Then app_path = app_path & ""ExlObj.Workbooks.Application app_path & "F-COST REPORT FY2004 Include MPG.xls"ExlObj.ActiveWorkbook.Sheets("DEPARTMENT 1").Activate

I have many function in my program.And theres the problem comes from.
In each function, i need to open the same workbook.But each function will pass values to different sheet in the workbook.
The problem is if i have 3 function, it will open 3 Excel workbook (i can know it via task manager in Windows),although it will display only one workbook because i put the

VB Code:
ExlObj.Application.Visible = True

only at the last function.
How to open only one same workbook but still i can send values to every sheet in the workbook???
Anyone here understand wat i mean??
Thanks

How Do I Connect To An Excel Workbook?
I'm writing a grade averaging program for a public school system and I wanted the results to be printed in an Excel spread sheet with each classes grades in a different sheet. For example Teacher A opens the program clicks a button that opens a new sheet for their first class. They then enter all info and save it. Next they go to the start of the program and start a new sheet for their second class and so on.

To do this I have some questions.

How do i connect my app to an excel work book?

How would I code a command button to create a new sheet with a certain template?

Excel Workbook Is Read Only
Hi,

I use the code below to read a column from an Excel spreadsheet. After closing the app and opening the spreadsheet from Excel, I get a message that the file is locked for editing. Only way to get rid of the lock is to restart the PC.

Any suggestions would be appreciated !

VB Code:
Private Sub Command1_Click() Dim EXL As New Excel.Application Dim exsheet As New Excel.Worksheet Set exsheet = EXL.Workbooks.Open("C: est.xls").Worksheets("Sheet1") Set exsheet = EXL.ActiveSheet Dim cell As String Dim x As Integer x = 1 'Place contents of column into a ListBox Do While EXL.ActiveSheet.Range("B" & x).Value() > 0 cell = EXL.ActiveSheet.Range("B" & x).Value() ListBox1.AddItem (cell) x = x + 1 Loop 'Close Excel EXL.Quit 'Close the spreadsheet, otherwise it will be locked Set EXL = Nothing Set exsheet = NothingEnd Sub

Getting Excel Workbook Type
IN VBA i want to know , the opened workbook 's format type i.e. whether its normal excel workbook , WK format , CSV format .. , DBF Format or any other format which Excel supports .. and i want to stop all other Save As format in Excel Save As Dialog box .. other than MS Excel format .. is there any way for not showing all those workbook types in Save As .. and if it saved in any other format , how i can come to know of which format/type this work book is .. any clue ??

Opening My Excel Workbook From VB
I have made a program that will take info from text boxes in VB and then inset the info in to cells in Excel.

My problem is that once the info has been added I want VB to open Excel and show the sheet which has just had the info added to it. This is what I have so far:

Dim x1app As excel.Application
Dim x1book As excel.Workbook
Dim x1sheet As excel.Worksheet

Set x1app = CreateObject("Excel.application")
Set x1book = x1app.Workbooks.Add
Set x1sheet = x1book.Worksheets.Add

x1sheet.Cells(1, 1).Value = Text1.Text
x1sheet.Cells(2, 1).Value = Text2.Text

x1sheet.Cells(3, 1).Formula = "r1c1 +r2c1"
Text3.Text = x1sheet.Cells(3, 1)

x1sheet.SaveAs "c: emp.xls"

x1app.Quit

Set x1app = Nothing
Set x1book = Nothing
Set x1sheet = Nothing

This will put the info in to a sheet but will not open it.
Can anyone please help.

Closing Excel Workbook
Dear Friends,

I wish to ask a simple question. I open an Excel file and read all things into array within my VB code. After that, I use

Set AppExcel = Nothing
Set wBook = Nothing
Set wSheet = Nothing

but I find the excel.exe is still runing at background in task manager of windows. When I explicitly open the xls file I just opened in the code before, Excel says can't edit, read-only, allow me to open read-only or Notify. That's very inconvenient.

How to close the file without any saving after I have read all cells a worksheet? Thanks

Excel - Workbook Edit

Does a workbook have to be open to be editted? If so, are there any suggestions on the easiest/fastest way to open and close workbooks for editing (the workbooks are big and take some time to open and close). Thanks.