STUCK! attachmate macro, excel to extra

[mizsydney]

I most humbly apologize in advance for my lack of knowledge, I'm sure my questions are painfully obvious to you guys!

I am trying to set up a macro that will scrape a cell value from excel, send to extra & <enter>, send more cells once the Extra screen comes up, <enter> and keep going row by row,until the next row is blank.

I have begged/borrowed/stolen scraps of macro code from this site and got something that sorta works, but I'm not understanding some basic concepts. the Help files for both excel VBA and Extra sort of assume you know what you're doing so they haven't been helpful to me. and recording hasn't helped me either.

whatever I'm doing isn't sending the value from excel to extra. I *had* it sorta working (it was sending the value to extra etc but I hadn't figured out how to loop until the row was blank), but (based on info I read here) I went back and specified the worksheet instead of relying on activesheet.

this worked:

xlApp.Workbooks.Open FileName:="C:\MHNAR.xls" 
        Set xlSheet = xlApp.activesheet
        Set APNMyRange = xlApp.activesheet.Range("G5")

this doesn't:

   xlApp.Workbooks.Open FileName:="C:\MHNAR.xls" 
        Set xlSheet = xlApp.worksheets("MH")
        Set APNMyRange = xlApp.xlSheet.range("G5")

and I have no experience with VBA so none of this makes any sense to me. (in my limited defense I could write code for the OLD extra basic in my sleep.)

another issue I have is that excel stays "open" when I'm done, I have no idea how to "clear" it. I'd like to have __xlApp.Visible = True__ be False because we don't need to have excel open, but when I change to False I can only see excel running in task Mgr and have to close it that way, attempts to open the file are read-only because the file is already open.

could someone please tell me what I am missing? I realize that these questions must be pretty "d'oh!" for you, but I am just not finding what I need to do it alone.

thanks in advance.

 

[SkipVought]

Hi,

You are writing your procedure in Extra Basic. Extra Basic is much less robust than Excel VBA. Of course, I'm biased, as I write everything i do in Excel VBA and some in MS Access VBA.

However, back to the questions at hand. Note that when you set an object, it inherits the properties of that object, so REUSING the xlApp is like referencing it TWICE...

  dim xlWB as Excel.Workbook
  set xlWB = xlApp.Workbooks.Open FileName:="C:\MHNAR.xls" 
  Set xlSheet = xlWB.worksheets("MH")
  Set APNMyRange = xlSheet.range("G5")

"excel stays "open" when I'm done"

xlWB.close
set xlWB = Nothing

xlApp.Quit
set xlApp = Nothing

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[vzachin]

hi,

did you take a look at this recent post

http://www.tek-tips.com/viewthread.cfm?qid=1524823&page=1

zach

 

[mizsydney]

thanks zach, I did! in fact that was where I swiped most of my code.

if I better understood VBA that code would be almost perfect, but I am so darned dim that even Skip's answers didn't help. I'm clueless as to why I have to set an object, much less understand why setting it twice is a problem.

Skip's answers are giving me syntax errors, so clearly something else is wrong in my code. some dim or set or some other small word I don't understand yet.

I'm going back to find an old working version, and start tweeking it again. I'm getting out my copy of "excel VBA for dummies" and re-reading, hopefully I can return later with more intelligent questions.

thanks for your help!

 

[vzachin]

mizsydney,

i'm sure if you post your code and explain exactly what you're trying to accomplish, you will find the help here whether you want to run the code from attachmate or run the code from excel.
i have learned a lot from skip and others who post back with their help.

i do most of my coding with excel vba after reading about it here. my coding is sloppy but it gets the job done. there are many ways to write code, just some are more efficient than others.


zach