?
Solved

How to transfer input data to Excel form

Posted on 1998-09-15
6
Medium Priority
?
246 Views
Last Modified: 2010-04-30
Once all the necessary information is inputed the information must be placed on a Excel form that has already been made. How do I code this to transfer the information to the appropriate space on the excel form?
0
Comment
Question by:okuda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 

Expert Comment

by:NateT
ID: 1435075
where is the info originating from?
0
 

Author Comment

by:okuda
ID: 1435076
info is originating from the input of several forms within my application. All information is being sent to a temp form prior to placing on the excel form or workbook sheet. On the Temp form Cmd Button to print should send the information to the excel workbook sheet at a specific location. ie name first, number second, level third, comments fourth etc. This program is to assign a bed and cell to a young offender within a specific unit. Cell 1A being first line Cell 1B being second line. If there is an email address that I may send a copy of the excel worksheet to so that you can see what I mean please let me know...
0
 

Expert Comment

by:NateT
ID: 1435077
Go Ahead and send it to NTownsley@Darlingii.com
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Accepted Solution

by:
mark2150 earned 600 total points
ID: 1435078
Use the sendkeys command to drive the excel spreadsheet. You can force it to a specific cell and then send it one line at time. Go into excel and do the sequence by hand, noting every keypress you make. This forms the basis of your SendKeys script.

Here is a code excerpt I wrote to update an excel spreadsheet using this method:

'
' Launch Excel & give it focus. We'll try it later as a minimized app
'
retval = Shell("X:\MSoffice\office\EXCEL.EXE", vbNormalFocus)
AppActivate retval
DoEvents
'
' Tell Excel to open source file
'
AppActivate retval
SendKeys "%FO", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "%N" + srce$ + ".XLS", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "%O", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "^{HOME}", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "+^{END}", True
DoEvents
AppActivate retval
DoEvents
'
' Ask for HTML save
'
AppActivate retval
SendKeys "%FH~", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "{TAB} ~", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "%O", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "%A{home}{DEL 60}C:\VB\ROBOT.TEM~", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "%S", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "%A{home}{DEL 60}C:\VB\ROBOT.NEW~", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "~{esc 5}^{home}", True
DoEvents
AppActivate retval
DoEvents
'
AppActivate retval
SendKeys "%FXN~", True
DoEvents
AppActivate retval
DoEvents
'
' Wait here for Excel to catch up
'
On Error GoTo holder
'
' Name "robot.new" As "robot.xxx"
'
holdmore:
If FileLen("robot.new") < 1 Then GoTo holdmore
'
GoTo drain
' =================
'
holder:
Resume
'
drain:
End
'
End Sub

0
 

Author Comment

by:okuda
ID: 1435079
That seems to be way above the current level of V/B that I am currently at but I will give it a try and see if it works. If I have trouble at least I have a place to start...Thanks
0
 
LVL 12

Expert Comment

by:mark2150
ID: 1435080
It shouldn't be too hard. The SendKeys help file explains that all those %F's and {Esc}'s and such mean. If you can walk thru the spreadsheet *WITHOUT THE MOUSE* noting each and every key you press, you'll do fine.

If you look at the Excel menus, one option is usually underlined. <Alt> and that underlined character will pop up that option. So File, Open becomes <Alt F><O> Even simpler is to use the shortcuts listed on the menu. <Ctrl O> alone will pop the file open screen.

Take your time and run it full screen. You'll see Excel do it's thing. Watch closely and see where it zigs when you wanted it to zag and your macro will run just fine.

If you want to cut down on the VB code, create a macro in Excel and then just have VB fire *THAT* instead of having VB do every little stroke. This has the added advantage in that you can change the macro in Excel instead of having to recompile. Is not hard. Give it a shot. If you look at the code above, most of it is repeats of the same few lines:

Accepted Answer
    From: mark2150
                                                                                  Date: Tuesday, September 15 1998 - 11:22AM PDT

    Use the sendkeys command to drive the excel spreadsheet. You can force it to a specific cell and then send it one line at time. Go into
    excel and do the sequence by hand, noting every keypress you make. This forms the basis of your SendKeys script.

    Here is a code excerpt I wrote to update an excel spreadsheet using this method:

    '
    ' Launch Excel & give it focus. We'll try it later as a minimized app
    '
    retval = Shell("X:\MSoffice\office\EXCEL.EXE", vbNormalFocus)
    AppActivate retval
    DoEvents
    '
    ' Tell Excel to open source file
    '
    AppActivate retval
    SendKeys "%FO", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    AppActivate retval
    SendKeys "%N" + srce$ + ".XLS", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    AppActivate retval
    SendKeys "%O", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    AppActivate retval
    SendKeys "^{HOME}", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    AppActivate retval
    SendKeys "+^{END}", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    ' Ask for HTML save
    '
    AppActivate retval
    SendKeys "%FH~", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    AppActivate retval
    SendKeys "{TAB} ~", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    AppActivate retval
    SendKeys "%O", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    AppActivate retval
    SendKeys "%A{home}{DEL 60}C:\VB\ROBOT.TEM~", True
    DoEvents
    AppActivate retval
    DoEvents
    '
    AppActivate retval
    SendKeys "%S", True
    DoEvents
    AppActivate retval
    DoEvents
    '
 The "AppActivate retval" makes sure that Excel is hot. The "SendKeys..." sends *1* command at a time, and the DoEvents lines cede execution to the OS to give Excel a chance to digest the command.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

650 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