Code will not produce anything

[chan1]

Hello,
I have this section of code that is in a btnRun_Click Event and the code is attempting to take an excel spread sheet take the ranges of data which is currently about 5 columns with the word ‘test’ on the first row of each column and export it as a Pipe Delimited text file. The code I have doesn’t produce any errors at all but it will not produce any kind of new file. Does this look right? Thanks for any help or advice.

    
Imports Excel3 = Microsoft.Office.Interop.Excel

Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim xlApp As Excel3.Application
        Dim xlWorkBook As Excel3.Workbook
        Dim xlWorkSheet As Excel3.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value

        xlApp = New Excel3.Application
        xlWorkBook = xlApp.Workbooks.Open("C:\Documents and Settings\ramey.channell\My Documents\tryme.xlsx")
        xlWorkSheet = DirectCast(xlWorkBook.Sheets("Sheet1"), Excel3.Worksheet)
        xlWorkSheet.Select()

        xlWorkSheet.UsedRange.Copy()
        IO.File.WriteAllText("filename", Clipboard.GetText.Replace(vbTab, "|"))




    End Sub

[ForumAccount]

You might have to do:

xlWorkSheet.UsedRange.Select()

before:

xlWorkSheet.UsedRange.Copy()

[chan1]

Thanks!!!

[chan1]

Is it possible for me to take

xlWorkBook = xlApp.Workbooks.Open("C:\Documents and Settings\ramey.channell\My Documents\tryme.xlsx")

and instead of hard coding the document path have it set to

txtBrowse.text

which is a text box assigned to a browse button and afterwords have it save that same file to the path located in the text box but change the extension to a .ps1?

[ForumAccount]

Try something like this:

Public Class Form1
 
    Private Sub browseButton_Click(ByVal sender As System.Object, _
                                   ByVal e As System.EventArgs) _
                                   Handles browseButton.Click
        Using dialog = New OpenFileDialog()
            dialog.Filter = "Microsoft Excel Files (*.xls*)|*.xls*"
            If dialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Me.browseTextBox.Text = dialog.FileName
            End If
        End Using
    End Sub
 
    Private Sub executeButton_Click(ByVal sender As System.Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles executeButton.Click
        Dim path = Me.browseTextBox.Text
        If IO.File.Exists(path) Then
            Dim directory = IO.Path.GetDirectoryName(path)
            Dim resultsFilename = IO.Path.GetFileNameWithoutExtension(path)
 
            '//excel code, use 'path' instead of your hard-coded string
 
            Dim contents = New String() {}
            IO.File.WriteAllLines(String.Format("{0}\{1}.ps1", directory, _
                                                resultsFilename), contents)
        End If
    End Sub
 
End Class

[kareninstructor]

If there is the only place you are using Excel perhaps an alternate as in reading the range using Oledb

Private ConnectionNoHeader As String = _ 
 "provider=Microsoft.Jet.OLEDB.4.0; data source='{0}';Extended Properties=""Excel 8.0; HDR=No;"""

    Sub GetMyRange()
        ' FileName is the name of the Excel 
        ' file to open including a path if needed.
        Using MyConnection As New  _
            OleDbConnection(String.Format( _
                            ConnectionNoHeader, FileName))
            MyConnection.Open()

            ' Range columns will be Fx if you specify
            ' in the connection that there is no header.
            ' You could select all columns or less than
            ' all columns along with sorting data.
            '
            ' In this case MyRange1 has been predefined
            ' in the spreadsheet.
            Dim cmd As OleDbCommand = _
                New OleDbCommand( _
                <SQL>SELECT F1, F2, F3, F4 
                     FROM [MyRange1] 
                     ORDER BY F4 Desc</SQL>.Value, _
                     MyConnection)

            Dim dr As System.Data.IDataReader = cmd.ExecuteReader
            Dim dt As New DataTable With {.TableName = "exported"}
            dt.Load(dr)

            ' Build delimited string sample
            Dim sb As New System.Text.StringBuilder
            For Each row As DataRow In dt.Rows

                sb.AppendLine(String.Format("{0},{1},{2},{3}", _
                              row("F1"), _
                              row("F2"), _
                              row("F3"), _
                              row("F4")))
            Next

            ' Create file if needed or append to
            ' existing file
            My.Computer.FileSystem _
                .WriteAllText("somefile.txt", sb.ToString, True)

        End Using
    End Sub

[chan1]

Do I need to import anything. Currently getting this

'Forms' is not a member of 'Excel.Windows'

[ForumAccount]

It's not practical to require that users create a range on their spreadsheets just to use his application.

[chan1]

Currently I'm using this for my browse Button

    Private Sub btnBrowse_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBrowse.Click

        Using ofd As New OpenFileDialog
            ofd.Filter = ".xl files (*.xl*)|*.xl*"
            ofd.Title = "Select Hazmat Capture File"

            If ofd.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                lblFileName.Text = ofd.FileName

            End If

            txtBrowse.Text = ofd.FileName
        End Using


        'Using dialog = New OpenFileDialog()
        '    dialog.Filter = "Microsoft Excel Files (*.xls*)|*.xls*"
        '    If dialog.ShowDialog() = Windows.Forms.DialogResult.OK Then
        '        Me.txtBrowse.Text = dialog.FileName
        '    End If
        'End Using


    End Sub

And it seems to be working fine. I went and tried your execute

    Private Sub executeButton_Click(ByVal sender As System.Object, _
                                    ByVal e As System.EventArgs) _
                                    Handles executeButton.Click
        Dim path = Me.browseTextBox.Text
        If IO.File.Exists(path) Then
            Dim directory = IO.Path.GetDirectoryName(path)
            Dim resultsFilename = IO.Path.GetFileNameWithoutExtension(path)

            '//excel code, use 'path' instead of your hard-coded string

            Dim contents = New String() {}
            IO.File.WriteAllLines(String.Format("{0}\{1}.ps1", directory, _
                                                resultsFilename), contents)
        End If
    End Sub

End Class

And I was not able to get it to produce any files.

[ForumAccount]

You need to replace my commented section that says 'excel code' with your excel code. You also need to populate your own 'contents' object. My example shows WriteAllLines, but you can use WriteAllText too.

[kareninstructor]

It's not practical to require that users create a range on their spreadsheets just to use his application.

Never said they needed too. I was suggesting that perhaps using Excel libraries is prone to upgrade issues as I have seen this too many times which is why I suggested this or another option is to (if feasible) formulate the range yourself

Dynamically select a range of cells

Dim cmd As OleDbCommand = New OleDbCommand( _
    "SELECT * FROM [Sheet1$A16:D19]", MyConnection)

In the end it is best to have more than one option in your toolbox

[ForumAccount]

if feasible

We don't know the answer to this, however.

[chan1]

I will try this. When you are refering to the "Contents" are you meaning that I want to replace the vbTab with the |?

[ForumAccount]

Get rid of the contents variable and instead use what you were previously using:

IO.File.WriteAllText(String.Format("{0}\{1}.ps1", directory, _
                     resultsFilename), Clipboard.GetText.Replace(vbTab, "|"))

[chan1]

Sorry for all of the confusion. I was dropped into this task at work but this is what I have so far....

        Dim path = Me.txtBrowse.Text
        If IO.File.Exists(path) Then
            Dim directory = IO.Path.GetDirectoryName(path)
            Dim resultsFilename = IO.Path.GetFileNameWithoutExtension(path)

            '//excel code, use 'path' instead of your hard-coded string

            Dim xlApp As Excel3.Application
            Dim xlWorkBook As Excel3.Workbook
            Dim xlWorkSheet As Excel3.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value

            xlApp = New Excel3.Application
            xlWorkBook = xlApp.Workbooks.Open(path)
            xlWorkSheet = DirectCast(xlWorkBook.Sheets("Sheet1"), Excel3.Worksheet)
            xlWorkSheet.Select()

            xlWorkSheet.UsedRange.Select()
            xlWorkSheet.UsedRange.Copy()
            IO.File.WriteAllText(path, Clipboard.GetText.Replace(vbTab, "|"))

            IO.File.WriteAllText(String.Format("{0}\{1}.ps1", directory, _
                                                resultsFilename), Clipboard.GetText.Replace(vbTab, "|"))
        End If

[ForumAccount]

And does it work?

[chan1]

No. It will not give me any kind of output file. The segment of code that you had given me would work but that was when everything was hard coded.

[ForumAccount]

Step through the code, see what is happening. See if Clipboard.GetText() is returning what you think it should etc...

[chan1]

Okay....I stepped through the whole code and I hard coded the path in my txtBrowse text box just because that is the text file I am using and it give me this error 'IOException was unhandled' "The process cannot access the file 'C:\Documents and Settings\ramey.channell\My Documents\tryme.xlsx' because it is being used by another process." But the file isn't opened anywhere on my computer so I don't know where it could be used at so it won't go any farther than this line.

[chan1]

This is the line it is getting snagged on.

 IO.File.WriteAllText(path, Clipboard.GetText.Replace(vbTab, "|"))

[ForumAccount]

Open Task Manager > Goto Process > Check if there are any instance of excel.exe, end them if there are.

[vb_ftw]

IO.File.WriteAllText(path, Clipboard.GetText.Replace(vbTab, "|"))

the variable path is the path to your excel file. that statement is attempting to create a file on that path which is ur excel file...

delete or comment out that line...as the statement below is already doing what u want to do...

IO.File.WriteAllText(String.Format("{0}\{1}.ps1", directory, _
                                                resultsFilename), Clipboard.GetText.Replace(vbTab, "|"))

[chan1]

What is strange is when I hard code the path into the text box through the property manager it will work but if I leave the text box blank and then browse for it it will not work. Could it be because of how I how I have my browse button and text box setup? This is the current code for the txtBrowse and the btnBrowse_Click Procedure.

        Using dialog = New OpenFileDialog()
            dialog.Filter = "Microsoft Excel Files (*.xls*)|*.xls*"
            If dialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                Me.txtBrowse.Text = dialog.FileName
            End If
        End Using

[techgnome]

after browsing, does the text box contain the FULL PATH AND FILE NAME? Or just the file name? Sadly it's been too long since I've used the OpenFileDialog, so I'm not sure what it returns. shouldn't you be using the SaveFileDialog instead anyways?

-tg

[chan1]

It will return the whole path in the box. I don't want to use the savefiledialog because I want it to save automatically I don't want a box to pop up and then they choose where to save it. It will cause to much trouble for the user. The less interaction they have with it the better. I basically want it to spit out a pipe delimited file saved as a .ps1 file. But I just keep running into a few snags.

[techgnome]

OK... I see what's going on now. Got it.

Try this... just for squirts and giggles...

Dim strMyNewFile As String = String.Format("{0}\{1}.ps1", directory, resultsFilename)
MessageBox.Show(strMyNewFile)

Make sure that the path and name displayed is in fact what you expected it to be.

Then try this:

Dim myNewText As String = Clipboard.GetText
MessageBox.Show(myNewText) 
myNewString = myNewString.Replace(vbTab, "|")
MessageBox.Show(myNewText)

Make sure both message boxes display what you expect them to display.

If everything is good at that point... then put it all together:

IO.File.WriteAllText(strMyNewFile, strMyNewText)

-tg

[chan1]

Should I comment out everything I have so far?

    Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim path As String
        path = Me.txtBrowse.Text
        If IO.File.Exists(path) Then
            Dim directory = IO.Path.GetDirectoryName(path)
            Dim resultsFilename = IO.Path.GetFileNameWithoutExtension(path)

            Dim xlApp As Excel3.Application
            Dim xlWorkBook As Excel3.Workbook
            Dim xlWorkSheet As Excel3.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value

            xlApp = New Excel3.Application
            xlWorkBook = xlApp.Workbooks.Open(path)
            xlWorkSheet = DirectCast(xlWorkBook.Sheets("Sheet1"), Excel3.Worksheet)
            xlWorkSheet.Select()

            xlWorkSheet.UsedRange.Select()
            xlWorkSheet.UsedRange.Copy()
            'IO.File.WriteAllText(path, Clipboard.GetText.Replace(vbTab, "|"))

            IO.File.WriteAllText(String.Format("{0}\{1}.ps1", directory, _
                     resultsFilename), Clipboard.GetText.Replace(vbTab, "|"))
        End If



    End Sub

[techgnome]

If you comment it out, then it's not going to do what it does, then will it?

    Private Sub btnRun_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim path As String
        path = Me.txtBrowse.Text
        If IO.File.Exists(path) Then
            Dim directory = IO.Path.GetDirectoryName(path)
            Dim resultsFilename = IO.Path.GetFileNameWithoutExtension(path)

            Dim xlApp As Excel3.Application
            Dim xlWorkBook As Excel3.Workbook
            Dim xlWorkSheet As Excel3.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value

            xlApp = New Excel3.Application
            xlWorkBook = xlApp.Workbooks.Open(path)
            xlWorkSheet = DirectCast(xlWorkBook.Sheets("Sheet1"), Excel3.Worksheet)
            xlWorkSheet.Select()

            xlWorkSheet.UsedRange.Select()
            xlWorkSheet.UsedRange.Copy()

' -- ADD this FIRST
Dim strMyNewFile As String = String.Format("{0}\{1}.ps1", directory, resultsFilename)
MessageBox.Show(strMyNewFile)

' -- When that works, then add this
Dim myNewText As String = Clipboard.GetText
MessageBox.Show(myNewText) 
myNewString = myNewString.Replace(vbTab, "|")
MessageBox.Show(myNewText)

' -- When that works, then add this....
IO.File.WriteAllText(strMyNewFile, strMyNewText)

' -- Now check your file, see if it exists with the data or not.

        End If



    End Sub

-tg

[chan1]

I was never able to get a message box to show up.

[vb_ftw]

maybe u r clicking btnRun before btnBrowse. if there is no text in the textbox, the code in btnRun will not execute because it checks if the path exists...

i really see no need for this, the code works perfectly fine... and if ur not getting any out u might try using an index value like 1 instead of "Sheet1" here:

xlWorkSheet = DirectCast(xlWorkBook.Sheets("Sheet1"), Excel3.Worksheet)

[chan1]

I will browse for the file and it will post the path in the txtBrowse textbox then I will click the btnRun. I will try the index value of 1 instead of "Sheet1"

[chan1]

I was able to get it to work. But for the second part of the code I need to declare my new string somewhere don't I?

[vb_ftw]

what's the second part of the code?

[techgnome]

You tell us... I'm no longer sure what you're talking about...
What second part? what "my new string" are you talking about?
Are you talking about the myNewText in the code I added? I dimed everything I used...

-tg

[chan1]

I was able to get it to work. In one of the other files in the project that it was referencing to it had something that it didn't like. Thanks for all of y'alls help.

[vb_ftw]

glad i could contribute...