[RESOLVED] First thread in this site, problem with reading from excel,help~

[boris9050]

Firstly I have to tell u that I am a Chinese boy, so maybe I will not quite understand what u experts have responded and ask again for solution, sorry for that.
Well, to the problem. I am trying to read from an Excel file including 2 cols and unsure rows, and I wanna get all these rows to be written into my access file. Firstly, I have to ensure that none of these rows can be null. I wrote these codes, yet the problem shows now:
1. Only when I open the file manually can the file be accessed by my application. (The excel file is in MyDocument folder, can it be the problem of permission that refused me???)
2. I found quite a lot of copies of application "Excel.exe" running in my processes explorer.(This happens when I tested for quite a time). I cannot quit this application????

Public Function ValidateExcelFile(strFilePath As String) As Boolean
    Set appExcel = New Excel.Application
    Set wbExcel = appExcel.Workbooks.Open(strFilePath)
    Set wsExcel = wbExcel.Sheets(1)
    Dim lRowNum As Long
    Dim lColNum As Long
    lColNum = wsExcel.UsedRange.Columns.Count
    lRowNum = wsExcel.UsedRange.Rows.Count
    If lColNum <> 2 Then
        ValidateExcelFile = False
        appExcel.Quit
        Set appExcel = Nothing
        Set wbExcel = Nothing
        Set wsExcel = Nothing
        'Exit Function
    Else
        Dim i
 
        For i = 2 To lRowNum
            If wsExcel.Cells(i, 1).Value = "" Then
                ValidateExcelFile = False
                Err.Raise "100", , modWizard.GetResString("3004")
                appExcel.Quit
                Set appExcel = Nothing
                Set wbExcel = Nothing
                Set wsExcel = Nothing
                Exit Function
            End If
        Next
        ValidateExcelFile = True
        appExcel.Quit
        Set appExcel = Nothing
        Set wbExcel = Nothing
        Set wsExcel = Nothing
    End If
    
End Function

Hope you guys can help me~~~A help from China )

Thanks.

[Static]

welcome!

hmm.. im not sure why it wont work.. it looks good to me.
are u getting any errors?
(if so.. on what line)

[si_the_geek]

Welcome to VBForums!

First of all, your English is very good - so hopefully you'll understand us (we can certainly understand you!).

1. I'm not sure why that would happen, your code seems fine. What does strFilePath contain?
Does the file have a password to open it?



2. This is happening because you aren't closing the objects (wbExcel etc) properly. This is even more so when you raise the error (Err.Raise), as this immediately exits the loop - without running the "quit" code you have.

Here is an amended version of your code, which should remove that issue:

Public Function ValidateExcelFile(strFilePath As String) As Boolean
        Set appExcel = New Excel.Application
        Set wbExcel = appExcel.Workbooks.Open(strFilePath)
        Set wsExcel = wbExcel.Sheets(1)
        Dim lRowNum As Long
        Dim lColNum As Long
        lColNum = wsExcel.UsedRange.Columns.Count
        lRowNum = wsExcel.UsedRange.Rows.Count
        If lColNum <> 2 Then
            ValidateExcelFile = False
        Else
            Dim i
 
            For i = 2 To lRowNum
                If wsExcel.Cells(i, 1).Value = "" Then
                    ValidateExcelFile = False
                    Set wsExcel = Nothing
                    wbExcel.Close  SaveChanges:= False
                    Set wbExcel = Nothing
                    appExcel.Quit
                    Set appExcel = Nothing
                    Err.Raise "100", , modWizard.GetResString("3004")
                    Exit Function
                End If
            Next
            ValidateExcelFile = True
        End If
'as this was being done at the end of both parts of the "If", you can simply have it afterwards
        Set wsExcel = Nothing
        wbExcel.Close  SaveChanges:= False
        Set wbExcel = Nothing
        appExcel.Quit
        Set appExcel = Nothing
        
    End Function

I would recommend stopping all instances of Excel.Exe in task manager before running it again - it may well solve the first problem too.

[Static]

ahh.. LOL. after a 10 day vacation to the beach.. I completely missed those!

[boris9050]

welcome!

hmm.. im not sure why it wont work.. it looks good to me.
are u getting any errors?
(if so.. on what line)

Thank you, feeling happy to hear that welcome~

I do get errors, yet I've used an ErrorHandler, which u know, sometimes will not display the line error happened. And its technical description is a bit diffcult for me to translate, sorry:<

[boris9050]

Welcome to VBForums!

First of all, your English is very good - so hopefully you'll understand us (we can certainly understand you!).

1. I'm not sure why that would happen, your code seems fine. What does strFilePath contain?
Does the file have a password to open it?



2. This is happening because you aren't closing the objects (wbExcel etc) properly. This is even more so when you raise the error (Err.Raise), as this immediately exits the loop - without running the "quit" code you have.

Here is an amended version of your code, which should remove that issue:

Public Function ValidateExcelFile(strFilePath As String) As Boolean
        Set appExcel = New Excel.Application
        Set wbExcel = appExcel.Workbooks.Open(strFilePath)
        Set wsExcel = wbExcel.Sheets(1)
        Dim lRowNum As Long
        Dim lColNum As Long
        lColNum = wsExcel.UsedRange.Columns.Count
        lRowNum = wsExcel.UsedRange.Rows.Count
        If lColNum <> 2 Then
            ValidateExcelFile = False
        Else
            Dim i
 
            For i = 2 To lRowNum
                If wsExcel.Cells(i, 1).Value = "" Then
                    ValidateExcelFile = False
                    Set wsExcel = Nothing
                    wbExcel.Close  SaveChanges:= False
                    Set wbExcel = Nothing
                    appExcel.Quit
                    Set appExcel = Nothing
                    Err.Raise "100", , modWizard.GetResString("3004")
                    Exit Function
                End If
            Next
            ValidateExcelFile = True
        End If
'as this was being done at the end of both parts of the "If", you can simply have it afterwards
        Set wsExcel = Nothing
        wbExcel.Close  SaveChanges:= False
        Set wbExcel = Nothing
        appExcel.Quit
        Set appExcel = Nothing
        
    End Function

I would recommend stopping all instances of Excel.Exe in task manager before running it again - it may well solve the first problem too.

Thank you sir. I will try that immediately!
Thanks, really!

[boris9050]

By the way, I don't have a password to open, and strFilePath, as is defined, contains the real path of the file, gained from a textBox with a button to open a OpenDialog.

[boris9050]

I found the problem,yet havn't resovled it :<
The problem happens here:

Set wbExcel = appExcel.Workbooks.Open(strFilePath)

and VB says :"Cannot access The file book2.xls." and the app stops.

Why that happens??? Confused~

[si_the_geek]

Add the line just before that one:

Debug.Print strFilePath

..and tell us what gets printed to the Immediate window (if you cant see the Immediate window, select it from the "View" menu).

[boris9050]

it says :"C:\Documents and Settings\Admin\My Documents\Book2.xls"

can it be a permission-refused problem, or something?

Thanks~

[RobDog888]

Do you have access/permission to the Admin profile's Documents?

[si_the_geek]

It could be, but then you wouldn't be able to open it manually either.

Could another program have the file open at the same time? (if so, that would probably cause the problem).

[Static]

open your task manager and check to make sure NO EXCEL's are running...
if its open elsewhere it may cause a problem...

then if it still doesnt work.. copt the book to C:\
and try it from there... "C:\Book2.xls"

[boris9050]

Well, it can't be the permission problem, I tried to read another file from Drive D,which is not an NTFS file system, but it still not works

[boris9050]

There isn't any Excel.exe instance in my task manager, and I tried in another Disk, but problem remains :<<<<

[si_the_geek]

Actually... as you aren't writing to the file, you could open it as read-only, using code like this:

Set wbExcel = appExcel.Workbooks.Open(strFilePath[u], 0, True[/u])

[boris9050]

By the way, VB says also :"Runtime error:1004", can this help you guys figure that out?

sighhhh~

[boris9050]

OK, try it immediately~

[boris9050]

Oh, faint~This time, the problem "updates" as that:
"Runtime error:1004, Cannot get 'Open' attribute in Class Workbooks." --->In English basicly means that.

[RobDog888]

Just read most of the thread. Are you using a non-english version of Excel or Windows? Which version of Excel are you using?

If you copy the xls file to the root of C will it open? It doesn break on error at the .Open line of code?

[boris9050]

I am using a Chinese-simplified version of both Windows and Excel, and VB as well.
I am using Excel 2000.
I added a Reference to Microsoft Excel Object Library 9 in VB.
I still cannot open the Excel file using my app, even if copied to the root C. Yet can be opened of course in Excel itself

[RobDog888]

Hmm, its got to be the chinese version of Excel possibly. Just a hunch as I have seen it behave badly before on certain chars causing issues.

Do you have both Windows and Office current Updates?

[boris9050]

Well, Thank you guys, really~
I think I get it now. The murderer is another Function IsExcelFile
As I wanna check whether the file input is Excel before reading it, I wrote this function as follows:

Public Function IsExcelFile(strFilePath As String) As Boolean
    Dim connStr As String
    connStr = ConnStringHead & strFilePath & ConnStringEnd
    dim sql 
    sql= "select * from [Sheet1$A2:A100]"
 
    On Error Resume Next
    
    adoConn.Open connStr
    
    If Err Then
        IsExcelFile = False
        Exit Function
    Else
        IsExcelFile = True
    End If
        
'--------------------------------------------these two lines are missing
        adoRS.Close
        adoConn.Close
'--------------------------------------------
End Function

And as you see, the connection hasn't been closed~~~

Yes~~~thank you for your help~~~really helpful to me!
By the way, welcome to China if possible~~~

[boris9050]

Hmm, its got to be the chinese version of Excel possibly. Just a hunch as I have seen it behave badly before on certain chars causing issues.

Do you have both Windows and Office current Updates?

Thank you sir, it's done~hoho

Love VB, love life~~~