Solved

Problem with the line

Posted on 2013-11-11
5
321 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SCCM 2012 Report 8 153
excel value = 0 change to another value 6 70
Best way to accomplish this task? 3 87
Is AWS.RDS considered a part of "Big Data"? 4 108
Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
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…
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…

773 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