What's Here?
- Members: 340,144
- Replies: 920,499
- Topics: 154,942
- Snippets: 4,855
- Tutorials: 1,257
- Total Online: 3,822
- Members: 126
- Guests: 3,696
|
 Welcome to Dream.In.Code |
|
|
Become an Expert!
Join 340,144 Programmers for FREE! Get instant access to thousands  of experts, tutorials, code snippets, and more! There are 3,822 people online right now. Registration is fast and FREE... Join Now!
Chat LIVE With a Expert
|
Editing Excel within VB code
Editing Excel within VB code
im new to this! help?
Rate Topic:
 - New D.I.C Head
-
-
Group:
New Members
-
Posts:
6
-
Joined:
11-November 08
Dream Kudos: 0
Posted 27 May 2009 - 11:50 AM
Greetings!
First off let me just say i have imports for the excel and office applications. I am using office 2007
I am hoping someone can show me what I am doing wrong here.
I have been searching and researching for a few days now on formatting excell cells within the VB code.
It works for most objects such as bold, font size, where i want to place the data and what I want contained in the cells.
I have been using the VBA within excel (macros) to determine the code i should use to format the cells, however, The underline feature doesnt work correctly as with any justification of the cells. or merging. I can't seem to make many of the format options work and am wondering if they are slightly different or if i need a few more code lines so that when i declare something like this...
Dim oExcel As Object
Dim oBook As Object
oExcel = CreateObject("Excel.Application")
oBook = oExcel.Workbooks.Add()
oSheet = oBook.Worksheets(1)
With osheet.range("A1:B1")
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
I get an error, xlcenter is not declared. I cant declare it as a string i just recieve a com error starting it couldnt be done.. I am stuck on how to get by these so called "undeclared" command strings that i need to format the cells properly.
Any help with this is greatly appreciated! 
Please let me know if any information required to assit me isn't listed here.
Posted 27 May 2009 - 10:29 PM
Used macro recorder to generate code and then modified as shown and worked fine:
With Worksheets("Teams").Range("A1:B1")
.HorizontalAlignment = xlCenter
End With
Did not use CreateObject as this was in an existing workbook.
The error msg is odd because vbLeft, vbBottom, etc. are VBA constants.
You might try: oSheet = oBook.Worksheets("Sheet1") or in place of Sheet1 the name you have given the sheet or even abandon the variables and refer directly to the worksheet as I did.
As a side question, does intellisense work for you?
Edit: My misunderstanding, when I saw Excel, assumed VBA, which is close to VB6 and since postings concerning VBA for Excel or Access do show up in this forum I went that path
This post has been edited by June7: 28 May 2009 - 09:16 PM
Posted 28 May 2009 - 09:57 AM
Tyler,
You went thru the correct step to setup an Excel object. One keyword of advise: "Do not use VBA code to VB.Net". VBA code and structure is more inline with VB6, and as such is very basic and has alot of implied constants that are only available in VBA. When going outside of the office suite for doing work, you need to go slow and steady. There are ways to get everything you want just when opening the files and operating in the files, it can be funky and potentially corrupt the file as a whole.
Back to the point, in using Office 2k7, you have one benefit over previous office products. All Office 2k7 files (minus Access and Publisher) store files in XML format. So in theory, once you find the XMLNS for the Excel framework, you should be able to parse the file and its contents and then affect the attributes of the areas you are wanting to "play" with.
What you need to do is
1) create a Excel object
- This will allow you to open Excell Files
2) then Workbook object
- This will allow you to change what Workbook you are in if you wanted to open another file and interact between the two
3) then Sheet object with the specified cells.
- This will allow you to work on a specified sheet in the workbook in case you have multiple sheets.
The key to know about the Worksheet is when you want to take a range, say A1 to D17, you will need to go one column more and 1 row more from the end, A1 to E18.
- New D.I.C Head
-
-
Group:
New Members
-
Posts:
6
-
Joined:
11-November 08
Dream Kudos: 0
Posted 28 May 2009 - 10:09 AM
 June7, on 27 May, 2009 - 10:29 PM, said:
Used macro recorder to generate code and then modified as shown and worked fine:
With Worksheets("Teams").Range("A1:B1")
.HorizontalAlignment = xlCenter
End With
Did not use CreateObject as this was in an existing workbook.
The error msg is odd because vbLeft, vbBottom, etc. are VBA constants.
You might try: oSheet = oBook.Worksheets("Sheet1") or in place of Sheet1 the name you have given the sheet or even abandon the variables and refer directly to the worksheet as I did.
As a side question, does intellisense work for you?
Yes intellisense works fine, The code though only appears to work in VBA (using excel) The code still gives me an error in VB.net code and the same occured for adding worksheets, however i finally got around that by declaring sheets as the object excel.workbook.worksheets or something to that effect.
I am sure this can work, if i can just figure out how to properly declare xlcenter and the other commands that do not work properly through VB.net. so that when they are used, and the command is sent correctly to format the cells, rather than giving me a build error.
I have used the VBA macro; record program, to get much of my excel code, its just that all of it doesnt work!! and i dont know why. Here are the imports I have for my VB program. perhaps i am missing one?
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Core
Imports System
Imports System.Reflection ' For Missing.Value and BindingFlags
Imports System.Runtime.InteropServices ' For COMException
Only the first 2 i added myself, others were either added via the VB.net program itself stating these are referenced if i wish to import. or through other forums where i found code involving excel.
I dont know why vb.net is being mean to me when i have imported the necessary reference files but any help here would be great.
Perhaps i am incorrect in assuming the VB.net is the same as the visual basic 2008 express download available from microsoft?
Also i tried your code, gives me error "worksheets" is a type and cannot be referenced
Posted 28 May 2009 - 10:39 AM
You are correct....VB.Net is the same as VS2k8Express. Your only problem is your trying to take VBA code and apply it in VB.Net. It DOES NOT WORK!!!
Again VBA has alot of assumed constants that are not available in VB.Net. Recording macros of the Excel variables and functions is not going to help you in VB.Net.
Unless you plan on coding in Excel...FORGET ABOUT VBA.
Now that i got that over, and i hope you got the point, do some google searches "vb.net excel object class" and see what you find.
here is one link that looks good for information: http://vb.net-inform...7_open_file.htm
- New D.I.C Head
-
-
Group:
New Members
-
Posts:
6
-
Joined:
11-November 08
Dream Kudos: 0
Posted 28 May 2009 - 10:54 AM
Ok that makes sense, although I don't quite understand why I can reference excel or Office core so specific code works if it wont allow me to use all of it. Thanks microsoft!
Anyway, I will continue to research this matter on my own then, however, if anyone should know how to format cells in excel from within VB.net i would appreciate any code snippets you can supply me.
Thanks for clearing up this VBA shenanigans for me
Posted 28 May 2009 - 11:08 AM
Its ok, they cant prevent you from accessing their application objects but they can limit what you can do since you are doing it outside.
Also, dont forget the the Express versions of Visual Studio will not be as robust as the standard and definitely more limited than the professional+ versions of VS. Most people use the Express versions for hobby and or educational learning.
What they didnt limit is your ability to add a reference to a library (DLL), if you can find it . Office 2k7 is version 12 so if you can find the program file you installed 2k7 in, you will be able to attach and copy the dll to your application and use the DLL in your application.
So if you smart, you can actually get the same effect as VS Pro, if you find the references and add them to your environment. Of course you have to find the references first
- New D.I.C Head
-
-
Group:
New Members
-
Posts:
6
-
Joined:
11-November 08
Dream Kudos: 0
Posted 28 May 2009 - 12:26 PM
woodjom, on 28 May, 2009 - 11:08 AM, said: What they didnt limit is your ability to add a reference to a library (DLL), if you can find it . Office 2k7 is version 12 so if you can find the program file you installed 2k7 in, you will be able to attach and copy the dll to your application and use the DLL in your application.
So if you smart, you can actually get the same effect as VS Pro, if you find the references and add them to your environment. Of course you have to find the references first
are you saying there is another reference besides the ones i have already referenced as shown above? or are you giving me a tip to reference software that might not be listed under the -->add reference option within the Vb.net program.
Posted 28 May 2009 - 12:37 PM
TylerD, on 28 May, 2009 - 02:26 PM, said: or are you giving me a tip to reference software that might not be listed under the -->add reference option within the Vb.net program.
you can reference just about any COM-visible DLL as long as you can find it. yes that means many windows base DLL's can be referenced and used from within even Express if you know where to find them and how to utilize them. Its not always easy find the usage of the DLL's, you will have to done serious deep research around the web for that information. As well, you will most likely need to get a DLL crack application, which is legal, to see what methods are available to you from the DLL.
I wasnt trying to confuse you but there is alot you can do with the express to simulate VSStd+ if you know where to get it and how to use it. Take it one step at a time, with each step will get you closer to enlightenment, but trying to jump straight to enlightenment will lead you to fall real hard.
Posted 28 May 2009 - 01:39 PM
TylerD, on 27 May, 2009 - 11:50 AM, said:
Try this working code just slightly adopted though
Tested on MS Office 2003, VS 2005
Imports System
Imports System.Collections.Generic
Imports System.ComponentModel
Imports System.IO
Imports System.Globalization
Imports System.Collections
Imports System.Data
Imports System.Drawing
Imports System.Text
Imports System.Threading
Imports System.Diagnostics
Imports System.Windows.Forms
Imports Excel = Microsoft.Office.Interop.Excel
................................
Private Sub btnStyle_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStyle.Click
Try
Dim strFileName As String = "C:\FormatCellExample.xls"
Dim xlApp As Excel.Application = New Excel.ApplicationClass()
Dim xlWorkBook As Excel.Workbook = Nothing
Dim xlWorkSheet As Excel.Worksheet = Nothing
Dim xlRange As Excel.Range = Nothing
Dim misValue As Object = System.Reflection.Missing.Value
'' * to open existing file *
'xlWorkBook = xlApp.Workbooks.Open(strFileName, misValue, misValue, misValue _
' , misValue, misValue, misValue, misValue _
' , misValue, misValue, misValue, misValue _
' , misValue, misValue, misValue)
'' * to add the new one *
xlWorkBook = xlApp.Workbooks.Add() '(Excel.XlFileFormat.xlTemplate)
xlWorkBook = xlApp.ActiveWorkbook
xlWorkSheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)
xlApp.ScreenUpdating = False
xlApp.DisplayAlerts = False
Try
xlApp.ScreenUpdating = False
'
' * create style *
'
Dim xlStyles As Excel.Styles = xlWorkBook.Styles
Dim xlStyle As Excel.Style = Nothing
Dim isstyleexists As Boolean = False
'
' * check if this style exist *
'
For Each xlStyle In xlStyles
If xlStyle.Name = "MyNewStyle" Then
isstyleexists = True
Exit For
End If
Next
'
' * if this does not exist so add new one *
' ' get Range "A1"
If (Not isstyleexists) Then
xlStyles.Add("MyNewStyle")
xlStyle = xlStyles.Item("MyNewStyle")
' Important: save changes !
xlWorkBook.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue) '
With xlStyle
.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
.Font.Size = 12
.Font.Bold = True
.Font.Italic = True
.Font.Name = "Times New Roman"
.Font.ColorIndex = 11
.Interior.Color = 16764057
.WrapText = True
'etc, etc
End With
End If
' [ NOTE: you can change by this way as I show above the properties
' of any cells and diapazones also ]
'
' * first way to write data to cell *
'
' get Range "A1"
xlRange = CType(xlWorkSheet.Cells.Range("A1"), Excel.Range)
xlRange.Value2 = "Editing Excel within VB code, im new to this! help?"
'
' * and at the end use this style for cell A1 *
'
xlRange.Style = "MyNewStyle"
xlApp.ScreenUpdating = True
'
' * save book *
'
xlWorkBook.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue) '
' * close book *
'
xlWorkBook.Close(True, misValue, misValue)
'
' * quit Excel *
'
xlApp.Quit()
'
MessageBox.Show("Data exported to " & strFileName)
Catch ex As System.Exception
MessageBox.Show(ex.Message & "\n\n======= WRITE TO EXCEL ERROR: ======\n\n" & _
ex.StackTrace)
Finally
' clean up memory
releaseObject(xlRange)
releaseObject(xlWorkSheet)
releaseObject(xlWorkBook)
releaseObject(xlApp)
End Try
Catch exl As System.Exception
MessageBox.Show(exl.Message & _
"\n\n======= ERROR TO ACESS EXCEL: ======\n\n" & _
exl.StackTrace)
End Try
End Sub
'
' * clean up *
'
Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As System.Exception
obj = Nothing
MessageBox.Show("Clean Up Memory Error\n" + ex.ToString())
Finally
If (Not obj Is Nothing) Then
Dim pos As Integer = GC.GetGeneration(obj)
GC.Collect(pos)
Else
GC.Collect()
GC.WaitForPendingFinalizers()
End If
End Try
End Sub
~'J'~
This post has been edited by fixo: 28 May 2009 - 09:43 PM
Posted 28 May 2009 - 04:01 PM
Fixo,
thats good for 2k, xp, and 2k3. Problem is that for 2k7, the file format is different and changes the access paths differently. With 2k7, you parse the file into a XML style object and then do what you performed. The benefit with 2k7 is that the parsing is faster than 2k3 and below. I had a file that was just over 500 lines in 2k3 and it would take 2 minutes to load the file, parse what i needed and then update a database from said file. i tested it only 2k7, and it took just around 30 secs to do the same operation on a 2k7 file. Unfortunately i wasnt able to save the code for my further purposes and the company i worked for at the time has the code files for the 2k7.
Posted 28 May 2009 - 09:46 PM
woodjom, on 28 May, 2009 - 04:01 PM, said: Fixo,
thats good for 2k, xp, and 2k3. Problem is that for 2k7, the file format is different and changes the access paths differently. With 2k7, you parse the file into a XML style object and then do what you performed. The benefit with 2k7 is that the parsing is faster than 2k3 and below. I had a file that was just over 500 lines in 2k3 and it would take 2 minutes to load the file, parse what i needed and then update a database from said file. i tested it only 2k7, and it took just around 30 secs to do the same operation on a 2k7 file. Unfortunately i wasnt able to save the code for my further purposes and the company i worked for at the time has the code files for the 2k7.
Thanks for the info
Soon I will be able to install 2k7 on my home machine
so this information is very important for me
Regards,
~'J'~
- New D.I.C Head
-
-
Group:
New Members
-
Posts:
6
-
Joined:
11-November 08
Dream Kudos: 0
Posted 29 May 2009 - 08:45 AM
woodjom, on 28 May, 2009 - 12:37 PM, said: TylerD, on 28 May, 2009 - 02:26 PM, said: or are you giving me a tip to reference software that might not be listed under the -->add reference option within the Vb.net program.
you can reference just about any COM-visible DLL as long as you can find it. yes that means many windows base DLL's can be referenced and used from within even Express if you know where to find them and how to utilize them. Its not always easy find the usage of the DLL's, you will have to done serious deep research around the web for that information. As well, you will most likely need to get a DLL crack application, which is legal, to see what methods are available to you from the DLL.
I wasnt trying to confuse you but there is alot you can do with the express to simulate VSStd+ if you know where to get it and how to use it. Take it one step at a time, with each step will get you closer to enlightenment, but trying to jump straight to enlightenment will lead you to fall real hard.
Thank you for this information, I will research as time permits. I also look forward to falling real hard, tend to learn more when pain is involved.
- New D.I.C Head
-
-
Group:
New Members
-
Posts:
1
-
Joined:
14-October 09
Dream Kudos: 0
Posted 14 October 2009 - 05:49 AM
This Worked For Me
ws.Range(rng).VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
ws is the worksheet name
and rng is the name of the range
This post has been edited by russellnunes: 14 October 2009 - 05:53 AM
1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users
|
Be Social
Programming
Web Development
Reference Sheets
Bye Bye Ads
Monthly Drawing
Top Contributors
Top 10 Kudos This Month
|
Add Reply
MultiQuote
| 
