By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,375 Members | 3,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,375 IT Pros & Developers. It's quick & easy.

Reading/Writing to excel

P: 30
How do I make vb 2005 read and write to a specified cell in excell 2003 & 2007.
Jan 9 '08 #1
Share this Question
Share on Google+
5 Replies


kadghar
Expert 100+
P: 1,295
How do I make vb 2005 read and write to a specified cell in excell 2003 & 2007.
have you searched around the forum? there are many examples of that. You can use an object with an excel application:
Expand|Select|Wrap|Line Numbers
  1. dim obj1 as object
  2. dim a as variant 'or object, i dont remember how it's defined in vb2005
  3. dim b as string
  4. set obj1 = createobject("excel.application") 'I think "set" is not used in vb 2005, so it'll remove it.
  5. obj1.workbooks.open "c:\mybook.xls"
  6. obj1.cells(1,1) = "hello":obj1.cells(1,2) = "goodbye"
  7. with obj1.worksheets(1)
  8.     a=range(.cells(1,1), .cells(1,1).end(-4121)) '-4121 is the number of the constant xldown, but it's not recognized by all the versions of vb, so -4121 or -4161 for xlleft will help.
  9.     b=.cells(1,1).value
  10. end with
you might have some troubles while closing the application, so what i'll recomend you to do, in order to avoid generating trash is to make it visible before you close it:

Expand|Select|Wrap|Line Numbers
  1. obj1.visible=true
  2. ob1.quit  'or was it close instead of quit?  i'll have to check that out.
HTH

Note: when you use obj1.cells(y,x) it'll asume you're using the "activebook" and "activesheet" the "right complete way" would be obj1.workbooks(1).worksheets(1).cells(1,1). Same for obj1.worksheets(1), it'll asume the workbook
Jan 9 '08 #2

P: 30
Thanks for the help
With obj1.worksheets(1)
a = range(.cells(1, 1), .cells(1, 1).end(-4121)) '-4121 is the number of the constant xldown, but it's not recognized by all the versions of vb, so -4121 or -4161 for xlleft will help.
b = .cells(1, 1).value
End With

I get a message saying range is not declared.
Jan 9 '08 #3

P: 30
I took range out and it works fine. Can you tell me how to read a cell now
this is how I read txt files
fileReader =
My.Computer.FileSystem.ReadAllText("C:\test.txt")
do i need to change the read all?
Jan 10 '08 #4

kadghar
Expert 100+
P: 1,295
I took range out and it works fine. Can you tell me how to read a cell now
this is how I read txt files
fileReader =
My.Computer.FileSystem.ReadAllText("C:\test.txt")
do i need to change the read all?
the second example reads only a cell

Expand|Select|Wrap|Line Numbers
  1. dim b as string
  2. b= obj1.cells(1,1)
while working with text files, i'd supose you want to read a tabs separated file. so the easiest way will be to save the file the way you're doing it, and then with the MID and INSTR functions take the "cell" you want.

Expand|Select|Wrap|Line Numbers
  1. fileReader=my.computer.filesystem.readalltext("c:\test.txt") 
  2. fileReader=mid(fileReader,1,instr(fileReader,chr(9))-1)
will give you the "first cell" of a tabs separated textfile

HTH
Jan 10 '08 #5

kadghar
Expert 100+
P: 1,295
Thanks for the help
With obj1.worksheets(1)
a = range(.cells(1, 1), .cells(1, 1).end(-4121)) '-4121 is the number of the constant xldown, but it's not recognized by all the versions of vb, so -4121 or -4161 for xlleft will help.
b = .cells(1, 1).value
End With

I get a message saying range is not declared.
I see, i think it's because range is a VBA function, try with

a= obj1.range(.cells(1,1) ...
Jan 10 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.