Solved

Problem with the line

Posted on 2013-11-11
5
320 Views
Last Modified: 2013-11-11
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?
0
Comment
Question by:HuaMinChen
  • 3
  • 2
5 Comments
 
LVL 20

Expert Comment

by:CompProbSolv
ID: 39640720
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39640731
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
 
LVL 20

Expert Comment

by:CompProbSolv
ID: 39640832
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
 
LVL 10

Author Comment

by:HuaMinChen
ID: 39640844
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
 
LVL 20

Accepted Solution

by:
CompProbSolv earned 120 total points
ID: 39640854
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Viewers will learn the different options available in the Backstage view in Excel 2013.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button a…

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now