[RESOLVED] open file in vb

[posto]

Sub mnuOpen_Click()
    Dim f As Integer            'for file number
 
    'This first group of commands invokes the Open Dialog
    ' OpenErr1 is invoked if user cancels from Open Dialog
    On Error GoTo OpenErr1
    ' and display the Open dialog (#1)
    CommonDialog1.ShowOpen
 
    ' then get the resulting filename
    FileName = CommonDialog1.FileName
 
    ' Now open a text file
    ' Set the real error handler for file errors
    On Error GoTo OpenErr2
    f = FreeFile
    Open FileName For Input As f
    txtBox.Text = Input$(LOF(f), f) ' Use the better code from the last lesson here
    Close f
 
    ' Nice touch: set the form's caption to contain the filename
    Form1.Caption = "Text Editor : " & FileName
 
    ' this exits if successful open and read
    Exit Sub
 
' this error handler for actual file errors
OpenErr2:
    MsgBox "Error opening file", vbOK, "Text editor"
    Close f
    Exit Sub
 
' this error simply exits if user Cancels from Open Dialog
OpenErr1:
    Exit Sub
 
End Sub

i've tried to use this command to open a file (word/excel) in vb but it said "error opening file".i was hoping that i could open it in vb itself (in text box or OLE or frame) and not in word/excel so that i can edit the data in vb.
can anyone help?

thanks!

[VBAhack]

If what you are doing is trying to open a Word or Excel doc as a text file, it won't work - the files are complex data structures. One way you could do it is use automation to invoke Word/Excel in the background and extract the data to put into your textbox. You can also open the files as binary, but without knowing the data structure of DOC or XLS, you wouldn't be able to interpret what you get. With Excel, there are a couple of ways to extract cell information from spreadsheets without having to actually open the file. Don't know if a similar capability exists for Word or not. The only way I'm aware of to read/write Excel files w/o using Excel itself is via ADO, but the information in the spreadsheet has to be arranged in database fashion.

[posto]

hey maybe that can help!

can you tell me how to do the excel "thing" plz and could you tell in detail as i'm stil new to this things so i could'nt understand the complex things in vb

thanks!

[VBAhack]

Following is the most simple ADO example I can think of. It reads data from an Excel file and puts it into an array. The data must be db fashion - data in each column must contain the same type data (string, number, etc.) but each column can be a different type of data. Exception: the columns can have text headers, which are ignored (unless all columns have text). Good luck.

If you want to know more, the following links might be helpful:

http://support.microsoft.com/default...b;en-us;278973
http://www.developerkb.com/modules/w...hp?category=23
http://www.w3schools.com/ado/default.asp
http://www.firstsql.com/tutor2.htm

Sub ReadExcelData()
    'requires a reference to the Microsoft ActiveX Data Objects library
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim cnString As String
    Dim strFileName As String
    Dim strDataSource As String
    Dim x As Variant
 
    strFileName = "F:\Stuff\TestData.xls"
    cnString = "DRIVER={Microsoft Excel Driver (*.xls)};ReadOnly=1;DBQ=" & strFileName
    strDataSource = "Sheet1$" 'Valid choices: "C1:C7", "Sheet1$", "Sheet1$C1:C7", "RangeName"
 
    Set cn = New ADODB.Connection
    cn.Open cnString
    Set rs = cn.Execute("[" & strDataSource & "]")
    x = rs.GetRows 'from Recordset to array
    PrintIt x
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub
 
Sub PrintIt(xArray)
    Dim i As Integer
    Dim j As Integer
    Dim strD As String
    
    For i = LBound(xArray, 2) To UBound(xArray, 2)
        For j = LBound(xArray, 1) To UBound(xArray, 1)
            strD = strD & "  " & xArray(j, i)
        Next j
        Debug.Print strD
        strD = ""
    Next i
End Sub

[si_the_geek]

As an alternative you can use Automation, see the Excel link in my signature for a tutorial.

[posto]

thanks a lot guys!i think that settles it