Problem with the line

Hi,
I do get this
https://dl.dropboxusercontent.com/u/40211031/t601.png
due to this line
                If Workbooks(flnm).Worksheets("Ite2").Cells(rw2, 1).Value = "" And Workbooks(flnm).Worksheets("Ite2").Cells(rw2, 2).Value = "" And Workbooks(flnm).Worksheets("Ite2").Cells(rw2, 3).Value = "" _
                    And Workbooks(flnm).Worksheets("Ite2").Cells(rw2, 4).Value = "" And Workbooks(flnm).Worksheets("Ite2").Cells(rw2, 5).Value = "" And Workbooks(flnm).Worksheets("Ite2").Cells(rw2, 6).Value = "" Then

Open in new window

while rw2 is 1. why does the error arise?
LVL 11
HuaMinChenBusiness AnalystAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
CompProbSolvConnect With a Mentor Commented:
It is somewhat tough to answer without seeing the entire code.  Nevertheless, I think I have the answer.

When you use: workbooks(flnm) it references a workbook that is open and flnm must be set to the name (not path and name) to it.

One solution would be to open the workbook and then access it with your code, except use:
If Workbooks(thefl).Worksheets("Ite2").Cells(rw2, 1).Value = "" Then
so you just have the file name and not the path.

If you want to access the data without opening the file, here are a couple of links:
http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm
http://spreadsheetpage.com/index.php/tip/a_vba_function_to_get_a_value_from_a_closed_file/
0
 
CompProbSolvCommented:
I think it would help troubleshooting this if you would separate all of the references to rw2 on separate lines to identify which one is causing the error.
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Sorry, even this line is causing the same error.
If Workbooks(flnm).Worksheets("Ite2").Cells(rw2, 1).Value = "" Then

Open in new window

while rw2 is 1.
0
 
CompProbSolvCommented:
I believe that the issue is that the worksheet does not exist as specified.

flnm is being used as a variable; what is its value?  Is it a workbook that has a worksheet in it named "Ite2"?
0
 
HuaMinChenBusiness AnalystAuthor Commented:
Thanks. I did open that Excel file successfully using this

    thefl = "file2.xlsx"
    flnm = vpath & "\" & thefl

    Application.Workbooks.Open (flnm)

Open in new window

and "Ite2" is the name of a worksheet in that workbook.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.