Solved

Problem with the line

Posted on 2013-11-11
5
317 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …
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…

762 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

7 Experts available now in Live!

Get 1:1 Help Now