Help in Word and Excel 1

[QuiltDaddy]

Hello, can anyone help me. I can't even get started. I can't find code that actually works anywhere for excel or word. I am using Visual Basic 2008. In Excel I have a spreed sheet already made. I want each cell to be a variable in an array that I can use in my program. In Word I have been able to create a new document from Visual Basic, but it won't let me add text to it because it says it is being used by another process. I'm just looking for the syntax to be able to utilize these methods. Can anybody help, this is very frustrating? Thanks.

 

[Christineeve]

What exactly are you trying to do?

 

[QuiltDaddy]

In Visual Basic 2008 I want code that will let me assign the text in an Excel cell to a variable in the program. In Word I have been able to create a new document from Visual Basic, but it won't let me add text to it because it says it is being used by another process. I'm just looking for the syntax to be able to utilize these methods.

 

[QuiltDaddy]

can anyone help?

 

[VB4Life]

Can you show me your Word automation code so I can see where you going wrong?

 

[PRPhx]

Let google be your friend. Search for excel vb.net. There are 4.5+ MILLION results, including samples.

 

[QuiltDaddy]

I have obviously already tried Google and that's how I got this far. Here is what I have for my word program:

Dim T() As String = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50)
Dim SolutionWriter As System.IO.StreamWriter
Dim Solution As String = ""
Dim SolutionFile As String = ""
Dim fso
Dim MyDocumentsPath As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments
Dim Folder As String = MyDocumentsPath & "\Solutions"
fso = CreateObject("Scripting.FileSystemObject")
SolutionFile = Folder & "\" & "Test" & ".doc"
SolutionWriter = IO.File.CreateText(SolutionFile)
If fso.FolderExists(Folder) = False Then fso.CreateFolder(Folder)
For Z = 1 To 50
If T(Z) = "" Then Solution += "." Else Solution += T(Z)
Next Z
SolutionWriter.WriteLine(Solution)

 

[VB4Life]

The easiest thing to do is Google 'vb.net word automation' and whatever you do - do not use CreateObject just use the new keyword and you will be fine. Remember to release the Com object or the process will not close.

 

[Sorwen]

First, what you have there has nothing to do with Excel or Word. I still cannot understand enough what you are wanting to do so I can't really help. You will want to look up on writing to word. I never do it so I can't tell you on that. On the Excel side look into either adding the Excel reference or how to use CreateObject to do it. The reason is that when write back and forth between two objects then you don't end up with a file lock. The way you have it there is a mixed up way (as you have vb.net and non-vb.net things there) to make a text file with a .doc extension which doesn't technically make a word document.

While this isn't the way you want to do it here at least is how a more vb.net way of doing what you wrote there.

    Private Sub WriteThis()
        Dim T() As String = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, _
                             20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, _
                             38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50}
        Dim SolutionWriter As System.IO.StreamWriter
        Dim Solution As String = ""
        Dim SolutionFile As String = ""
        Dim MyDocumentsPath As String = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
        Dim Folder As String = MyDocumentsPath & "\Solutions"

        If IO.Directory.Exists(Folder) = False Then IO.Directory.CreateDirectory(Folder)

        SolutionFile = Folder & "\" & "Test" & ".doc"
        SolutionWriter = New StreamWriter(SolutionFile, False) [green]'Or true.  You will want to look at it a decide.[/green]

        For Z As Integer = 0 To 49 'Arrays start at 0
            If T(Z) = "" Then
                Solution += "."
            Else
                Solution += T(Z)
            End If
        Next

        [green]'Generally you would use SolutionWriter.Write(Solution) instead of WriteLine, 
        'but I don't know what else you might be doing so WriteLine could be needed[/green]
        SolutionWriter.WriteLine(Solution)

        [green]'When you are finished with the file you have to close it.
        'That would be why it is locked.[/green]
        SolutionWriter.Close()
        SolutionWriter = Nothing

        MsgBox("Done") [green]'At some point in some way you want to tell yourself it is done. [/green]
    End Sub

There is one change I made that didn't have to be made and that was changing My.Computer.FileSystem.SpecialDirectories.MyDocuments. The only reason why is because it is less correct. It isn't incorrect, but I've heard that it was only added in because people couldn't seem to find the other method so better to show you the other way.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Sorwen]

Opps that should be For Z As Integer = 0 To [red]50[/red]. You messed up and had 1 to 50 and I mess up and do 0 to 49.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[realm174]

Or could you not use:

For Z As Integer = lbound(t) To ubound(t)

Then you don't need to worry about if your array starts at 0 or 1, right?



Cheers,

Realm174

 

[QuiltDaddy]

@ Sorwen >> Dead on! It worked beautifully! Thank you so much!

Now how about Excel? I want a program that can read from an excel spreadsheet. It just takes the value of each cell and gives those values to variables in an array.

 

[Sorwen]

You will want to search for some tutorials because the answer can get fairly complex and a bit more than I can go back and forth in answering because of that. If you get everything figured out on how you want to do it and it doesn't work then we can work with the code you come up with to get it working. If you know how to build what you want to get out of Excel in Excel (vba) first then getting that into VB.Net from there would be easy.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!