Edit already created excel book

Hi all, I have a problem. That is I can't figure out how to edit an already saved excel book. I am trying to make a program that inserts a load of formulas from a few values and organize them correctly but although I can do all that and save the sheet, I also need to be able to open already saved ones and edit them from there. I have only the very bare bones (if that) knowledge of automation (although I have decent knowledge of vb.net) and am generally guessing what to do for as far as I've come. All help is appreciated (and needed as I have a time limit as to when I can get this done). Thanks all

Welcome to the forum by_m :)

So you are making this automation with .NET or with VB6?
Also we will need more info about your project... have you already coded something?

Welcome to the forum by_m :)

So you are making this automation with .NET or with VB6?
Also we will need more info about your project... have you already coded something?
Thanks For replying rick. okay I'm making this in vb.net and I haven't coded anything but I have designed it. The reason I know about this is because I already hit the problem before in the same project but it got deleted so I started again. Now as for info: I know how to create a book and do basic stuff in the development environment but my problem is editing already created ones that have been saved on the hard drive or if not that at least getting off the information and putting it on another sheet that I'll make in the environment, or maybe you know of another way...
Anyway thanks again for replying and I hope that helps you understand my problem better

Ok, I'm not 100% sure what your issues are here, but to get started on Automation, you should have a read of the
Automating Office Programs with VB.Net / COM Interop (http://www.xtremevbtalk.com/showthread.php?t=160433) tutorial.

Then let us know what command(s) you may be having trouble with, but it sounds like you should be looking into the xlApp.Workbooks.Open() Event...

-- Mike

Ok, I'm not 100% sure what your issues are here, but to get started on Automation, you should have a read of the
Automating Office Programs with VB.Net / COM Interop (http://www.xtremevbtalk.com/showthread.php?t=160433) tutorial.

Then let us know what command(s) you may be having trouble with, but it sounds like you should be looking into the xlApp.Workbooks.Open() Event...

-- Mike
Ok thanks, thats what I was looking for. But now I wonder what to do with the statement. I type in the path and ignore all the other options because I don't know what the are (again I know practically nothing about automation) and it opens the excel sheet (but no data) and draws an error. I was just wondering what the required parameters are for the xlApp.Workbooks.Open() and how they work. Thanks

Here's the Help file for Excel 10.0Open method as it applies to the Workbooks object.

Opens a workbook.

expression.Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad, OpenConflictDocument)

expression Required. An expression that returns the Workbooks object.

FileName Required String. The file name of the workbook to be opened.

UpdateLinks Optional Variant. Specifies the way links in the file are updated. If this argument is omitted, the user is prompted to specify how links will be updated. Otherwise, this argument is one of the values listed in the following table.

Value Meaning
0 Doesn't update any references
1 Updates external references but not remote references
2 Updates remote references but not external references
3 Updates both remote and external references

If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks argument is 2, Microsoft Excel generates charts from the graphs attached to the file. If the argument is 0, no charts are created.

ReadOnly Optional Variant. True to open the workbook in read-only mode.

Format Optional Variant. If Microsoft Excel is opening a text file, this argument specifies the delimiter character, as shown in the following table. If this argument is omitted, the current delimiter is used.

Value Delimiter
1 Tabs
2 Commas
3 Spaces
4 Semicolons
5 Nothing
6 Custom character (see the Delimiter argument)

Password Optional Variant. A string that contains the password required to open a protected workbook. If this argument is omitted and the workbook requires a password, the user is prompted for the password.

WriteResPassword Optional Variant. A string that contains the password required to write to a write-reserved workbook. If this argument is omitted and the workbook requires a password, the user will be prompted for the password.

IgnoreReadOnlyRecommended Optional Variant. True to have Microsoft Excel not display the read-only recommended message (if the workbook was saved with the Read-Only Recommended option).

Origin Optional Variant. If the file is a text file, this argument indicates where it originated (so that code pages and Carriage Return/Line Feed (CR/LF) can be mapped correctly). Can be one of the following XlPlatform constants: xlMacintosh, xlWindows, or xlMSDOS. If this argument is omitted, the current operating system is used.

Delimiter Optional Variant. If the file is a text file and the Format argument is 6, this argument is a string that specifies the character to be used as the delimiter. For example, use Chr(9) for tabs, use "," for commas, use ";" for semicolons, or use a custom character. Only the first character of the string is used.

Editable Optional Variant. If the file is a Microsoft Excel 4.0 add-in, this argument is True to open the add-in so that it’s a visible window. If this argument is False or omitted, the add-in is opened as hidden, and it cannot be unhidden. This option doesn't apply to add-ins created in Microsoft Excel 5.0 or later. If the file is an Excel template, True to open the specified template for editing. False to open a new workbook based on the specified template. The default value is False.

Notify Optional Variant. If the file cannot be opened in read/write mode, this argument is True to add the file to the file notification list. Microsoft Excel will open the file as read-only, poll the file notification list, and then notify the user when the file becomes available. If this argument is False or omitted, no notification is requested, and any attempts to open an unavailable file will fail.

Converter Optional Variant. The index of the first file converter to try when opening the file. The specified file converter is tried first; if this converter doesn’t recognize the file, all other converters are tried. The converter index consists of the row numbers of the converters returned by the FileConverters property.

AddToMru Optional Variant. True to add this workbook to the list of recently used files. The default value is False.

Local Optional Variant. True saves files against the language of Microsoft Excel (including control panel settings). False (default) saves files against the language of Visual Basic for Applications (VBA) (which is typically US English unless the VBA project where Workbooks.Open is run from is an old internationalized XL5/95 VBA project).

CorruptLoad Optional Variant. Can be one of the following constants: xlNormalLoad, xlRepairFile and xlExtractData. The Default behavior if no value is specified is usually normal but may be safe load or data recovery, if Excel has already attempted to open the file. The first attempt is normal. If Excel stops operating while opening the file the second attempt is safe load. If Excel again stops operating the next attempt is data recovery.

OpenConflictDocument Optional Variant. True to open the local conflict document. Default is False. Mostly you usually only worry about the 'Filename' and 'Password' parameters (if the file is password protected).

If you don't have help within .Net, then you should open up Excel, then hit Alt-F11 to open up the VBE. Then Type "Workbooks.Open", click within the word "Open" and then hit the F1 key to get the Help file that I listed above. MSDN online is another source, but the VBA help files is usually the best/quickest first read.

-- Mike

Okay Thanks I'll Try That

Can you see any reason why the following code won't work because it draws a system.nullreferenceexception

Dim xlApp As Excel.Application
xlApp.Workbooks.Open("C:\Documents and Settings\Owner\My Documents\Text Files of All Kinds\Base Chart V1.xls")
xlApp.Visible = True

I'm not sure, but based on that, you haven't created a New Excel.Application yet. Try this:Dim xlApp As Excel.Application
Dim xlWB as Excel.Workbook
Dim sPath as String

xlApp = New Excel.Application
xlApp.Visible = True

sPath = "C:\Documents and Settings\Owner\My Documents\" & _
"Text Files of All Kinds\Base Chart V1.xls"
xlWB = xlApp.Workbooks.Open(sPath) -- Mike

alright thanks for your help with my problem. I hope that'll be all