Excel/Excel 2003 VBA
QUESTION: Dear Andrea,
I have a problem with how to use "Name" correctly in Excel 2003 Visual Basic when referring to a named cell. Below is code that will exhibit what I'm trying to do.
Statement Lines 1 & 2 - I open my test data file and select Cell A1.
In Line 3 I name the selected cell "StartRecord"
In Line 4, to validate that I have done things correctly,
MsgBox ActiveCell displays the contents of the Activecell (Cell A1) correctly.
But in Line 5, when I use MsgBox StartRecord, the MsgBox output displays blank.
Why doesn't Line 5 display the contents of Cell A1?
Please help. What am I doing wrong? How can I display the contents of a named cell?
Line 1 - Workbooks.OpenText Filename:="C:\MyTest\TestData.TXT"
Line 2 - Range("A1").Select
Line 3 - ActiveCell.Name = "StartRecord"
Line 4 - MsgBox ActiveCell
Line 5 - MsgBox StartRecord
ANSWER: Bob -
The first problem I come against is Line 1 - Why are you opening a text document?
Past that: if I ran the remainder of the code using the spreadsheet I had open, the message box returned correct. If I ran the code using the Line 1 to open an Excel workbook, it stopped at line 2, I believe because it needs more indication that it is looking for cell A1 in the recently opened sheet; however, I didn't want to go farther until I found out what the target of Line 1 is. That will make all the difference in the code.
Please let me know - where are you getting cell A1 from? - Andrea
---------- FOLLOW-UP ----------
QUESTION: Dear Andrea,
I have an Excel program which processes financial information. Some of the input data to my program is provided by my broker as a tab delimited format file. I take what I can get.
I really don't understand what you wrote about cell A1 needing a "target". When the .txt file is opened in my Line 1, cell A1 is automatically identified as the selected cell. However, just to be sure, I included Line 2. That makes cell A1 the selected file AND the Activecell which is named in Line 3.
Line 4 and 5 work as described in my original question.
Should I open the .txt file differently or do something else before naming cell A1? Please advise.
Right now your code asks to open the .txt file in Excel; but the remaining commands are not pointed at that newly opened file, they default to the same file that contains the VBA code - which is why you are returning what you are. That's why I asked you which A1 you were trying to look at, I wanted to know which spreadsheet you are trying to access with the VBA code.
You will need to refer to the .txt data spreadsheet that gets created for all of your commands, otherwise your code will be executing commands in the wrong spreadsheet. This will require Dim-ing a spreadsheet and range:
Dim wbk As Workbook
Dim rng As Range
Set wbk = Workbooks.Open("C:TestData.xls")
At this point you will be "acquiring" the wbk and using it for the rest of the code...and that's as far as I can get you, because there seems to be enough differences between 2003 and 2010 that I am not able to run the code to test it. I am sorry. Try another Expert who has 2003 on a machine, or do a search on this issue and you will probably be able to get the little bit you need to finish the job.
Hope this at least gets you in the right direction - Andrea