Help with inputing data into excel

[addiktion]

Thanks for the great program so far I'm just trying to figure out this out in my head.

Basically I have the macro locking onto a program at my work. The program connects to a MYSQL database. Inside the program it can generate a report of all open ticket issues via an excel spreadsheet.

I have it generate this report and save the document. I however need it to extract some data from the program and input it back into the excel document along the corresponding lines of the issue. For example the excel document looks like this.

Ticket | Followup

1 | Need data here

2 | Need data here too

3 | And here too

I have it go and extract ticket #: 1's follow up information from the program but I need to input it on the corresponding line in excel next to the ticket #. I can't use the "click over with mouse" method because each location will change based on which ticket number is being entered in with follow up data. Obviously the ticket numbers will vary as well.

Not only that but I figure I'm going to need a loop of some sort to recognize how many tickets are outputted to the excel document so it knows when to stop importing follow up data from the program.

Anyone have any tips on how I would go about doing this?

If anything is unclear I'll elaborate more. I'm Usually around between 10 PM to 7AM Mountain Standard Time.

[addiktion]

Also I'm unaware of how to use the DDEPoke command. I'm presuming this allows you to import data back into excel?

[addiktion]

Ok I just tested DDE Poke and it's definitely inserting stuff back into excel so I'll use this command to get data back into the spreadsheet with the corresponding ticket number.

I'm assuming I'll do something along the lines of.

Grab C1, R2 from excel sheet, go back to the program and input that into the search field, pulls up the follow up information from program, grab the follow up information and DDEPoke it back to C2,R2.

Next question.

How do I calculate how many rows of data an excel document has and put that into a loop so I know when to stop grabbing follow up information?

I'm guessing I'll need at least some type of counter in the loop to know which row I'm on. I might have an idea lol.. hold on.

[Marcus Tettmar]

>How do I calculate how many rows of data an excel document
>has and put that into a loop so I know when to stop
>grabbing follow up information?

I usually just loop until the returned cell string is an empty string.

[addiktion]

Awesome Thanks for the info Marcus.

I got it working

Now last question!

I have it set up in a dialog where Button 1 generates the report

and button 2 emails the report as soon as I'm done editing it, finally its sent via SMTP as an attached excel document

How Do I make sure button 2 isn't clicked first before button 1?

This way I can avoid dummy emails from being sent without a generated report.


By the way congrats on your baby Marcus

[addiktion]

I have a feeling I'll have to make changes in my MainLoop section in order for it to do an IF ELSE test on which button is clicked first.

[JRL]

You could "grey out" button 2 until button button 1 has been pressed. See the information in this thread.

[Marcus Tettmar]

If you don't want to "grey out" (disable) the button you could simply initialise a variable at the top of the script:

Let>DoneGenerateReport=0

Once you have finished generating the report do:

Let>DoneGenerateReport=1

And in the code for Button 2 (send email) do:

Code: Select all

If>DonwGenerateReport=1
  .. Do your code
Else
  .. either do nothing here or:
  MessageModal>Please generate the report first - there's nothing to send yet!
Endif

Disabling the button is nicer though.

[addiktion]

I've went ahead and enabled the disabled button. It works nicely

So far I've gotten the script to do everything I need it to do. I was having some weird issues with the SMTPSendMail command though. For some reason it wouldn't send the message with an attachment until I modified the line to read this:

SMTPSendMail>recipients,192.168.20.160,me,myname,subject,body,%filepath%;filepath,

I had to input the "variable" in both ways for it to send out. I tried testing it with just filepath; filepath, %filepath%; %filepath%, but nothing was working correctly until I added both lines in there. Now it works at least so I'm not to concerned with it.

It may be because of the small little program that I'm using to directly send out emails. It was a freebie but its reliable and fast but it wasn't "receiving" the file correctly at first until I altered that SMTP line. Not only that but I had to make sure the EXCEL document was closed else it will error out and not send.

Which makes sense to me but didn't cross my mind until I ran into the problem. But thanks Marcus and everyone who helped

[Bob Hansen]

Actually, you are using Macro Scheduler to sent your email, not the "small little freebie".

I suspect there might be an "explicit" problem, or a problem where you defined the filepath. If you provide the script we can help answer that.

Also, check for VAREXPLICIT value. See Help on "explicit".

[Dman]

So what would be the best way to simply have an xls and then somehow use macroscheduler to count the number of rows in it - without making any changes to it?

[Marcus Tettmar]

These articles should get you started:
http://www.mjtnet.com/blog/2007/07/02/m ... xcel-data/

With v10 you can suck in an entire spreadsheet with 3/4 lines of code and immediately get a row count:
http://www.mjtnet.com/blog/2008/04/16/r ... g-dbquery/