Solved

Problem with the line

Posted on 2013-11-11
5
338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 21

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 11

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 21

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 11

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 21

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

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…
Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
Suggested Courses

630 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