• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

CAn VBA MACRO be used to Move Data from Excel To Software?

X

xcruc1at3r

Member
We basically Place customer data in excel and then we update that data shipment one by one in the system..so is there any possibility of doing it with Macro? cause in the Software there is an Macro Option !!

like showing the path to the software from were to get the data and how and were to place it ! if i can upload the snaps of the software screens and the excel format would it help !??
 
Possibly...

There's a LARGE amount of unknowns with this problem. Depending on the software, there may be various libraries/references that would have to be used in XL.


On the other hand, the two ways of transferring data are:

a) Excel pushes the data to software

b) Software pulls from XL


B is probably the easier option, as software designers can easily setup their individual programs to access the more commonly used xls or csv files. Perhaps try a Google search to see if someone has done some macros, or other examples for the software you're using, and use that?
 
Luke the software we are using are made by IBM... a customized software....

it records macro as well i tried to record so it did... like i recorded a macro for login...so it did...

the recorded macro coding

End SubSub START_BOOKING()

With Session

.WaitForEvent rcEnterPos, "30", "0", 1, 1

.TransmitTerminalKey rcIBMPf9Key

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 5, 13

.WaitForDisplayString "==>", "30", 5, 9

.TransmitANSI "1"

.TransmitTerminalKey rcIBMEnterKey

.WaitForEvent rcKbdEnabled, "30", "0", 1, 1

.WaitForEvent rcEnterPos, "30", "0", 18, 33

.WaitForDisplayString "ID:", "30", 18, 29

.TransmitANSI "AKHIAAK"

.TransmitTerminalKey rcIBMTabKey


' Password has not been recorded for security

Dim hostpassword As String

hostpassword = ""

hostpassword = .GetPassword("PASSWORD:", "", "", "")

.TransmitANSI hostpassword

.TransmitANSI "50.13.9"

.TransmitTerminalKey rcIBMEnterKey

End With

End Sub


as i see it luke its a old software look are like DOS. do you want me to attach the snapshorts !?..so you can get the better idea?

in order to complete the shipment it takes 1 person around 14 hrs to complete the work :(...and company does not care!..
 
Hi ,


To add to what Luke has already mentioned , can you give more details ?


You say that at present , you are "placing" customer data in Excel , and then "updating" the system ; can you indicate how you are interacting with the IBM system ? Are you entering the same data in the IBM system using a data entry terminal ?


As Luke has mentioned you can have an online or an offline interaction between the two systems ; online where either your Excel worksheet will transfer data to the IBM system , or the IBM system will ask for and get data from the Excel worksheet ; is there any provision in the IBM system for offline interaction where data in files ( say .csv or .txt formatted files ) can be read by the IBM system ?


If you provide more details , even if you do not get a solution from this forum , you may get some ideas on how to get ahead.


Narayan
 
Yeah typically that sort of software has some sort of import option that will let pull in a csv or something.


care to share what the software is called?
 
Hi xcruc1at3r ,


I suggest you look at IBM-specific forums where you will find people who are probably doing such data transfers regularly , and may be in a better position to suggest a solution.


Narayan


P.S. I typed in Excel data transfer IBM in Google , and the following links came up :


1. http://ibmmainframes.com/archive/o_t__t_53334__view_next__index.html


2. http://www.itjungle.com/fhg/fhg101007-story02.html
 
well i have created a Excel Sheet with snaps of the Software and the sample Excel sheet format... kindly check.....

https://docs.google.com/spreadsheet/ccc?key=0Ah3qQ3ZqPOsPdENKNlNkOUZyN1FiSEtTU0JfRUxLS3c
 
is it possible to run macro in the software which should copy data from excel sheet and paste field ?!!...lets try it with 1 application window!.. let me attach the link for that...
 
Hi, xcruc1at3er!


As far as I could see that's an application running under an IBM 3270 susbsystem. I suggest you to contact your mainframe support team (or IT guys or whoever at customer service) and ask them if there's any interface available for what you want do do. If it exists, then you can tell Excel to export the -most probable- text file with the required format.


Briefly, begin for the mainframe side and see what's possible and what's not.


Regards!
 
well We have already approached them and they are saying whats benefit cost saving and would it cut the resource !... if they would do it so... that will send 1 guy home!!... bro... due to which i would like Chandoo's forums support!...so that i would help us decrease the Work Load... as i see it... Like Other macro's for retrieving/extracting data from 1 workbook to Main workbook sheet...by providing the path in the Main Work book Sheet so that it could retrieve/extract the data... so condition will apply... pls pls give it !! Try!! Guys...
 
something like this ...

Sub Collect_Trade_Data()


' Open Data Dump workbook

Workbooks.Open Filename:="c:pathsample.xls"

Windows("sample.xls").Activate


' Check if the first cell contains data. If not then close file

If Range("Sheet 1!A1") = "" Then

ActiveWorkbook.Close SaveChanges:=False

ActiveWorkbook.Saved = True


' If the cell does contain data then transfer the row of data across to the Summary file

Else

Range("A1:J1").Select

Selection.Copy

.......................

or like this ...

The GetValue Function


The GetValue function, listed below takes four arguments:


path: The drive and path to the closed file (e.g., "d:files")

file: The workbook name (e.g., "budget.xls")

sheet: The worksheet name (e.g., "Sheet1")

ref: The cell reference (e.g., "C4")


Private Function GetValue(path, file, sheet, ref)

' Retrieves a value from a closed workbook

Dim arg As String


.................

.. I have ..

.. Faith ..

.. in you all ..

.................
 
Hi, xcruc1at3r!


A comment, I'm sorry for the guy that'd be sent home, but I agree with IT guys.

Now to question, I don't see the specs for the file that the 3270 mainframe might import (ergo, the file to be exported from Excel).


Regards!
 
well the excel worksheet is based on 2 columns A & B only

well In A u'd Find Comtainer No. like this >>>AMFU3011682

and in B you will find SEAL : APA9916698 << like this... for your ref below are sum seals ( sorry for the late reply) you can create a Example excel file in my Doc.

AMFU3011682 APA9916698

AMFU3093649 APA9916508

AMFU3240903 APA9895014

AMFU3281461 APA9900408

AMFU3282365 APA9900266

AMFU8831426 APA9904120

AMFU8863763 APA9913740

AMFU8865642 APA9924214

AMFU8866736 APA9917181

APHU6025701 APA9917177

APHU6084279 APA9900448

APHU6100739 APA9910635

APHU6104610 APA9916503

APHU6107857 APA9910634

APHU6146745 APA9896373

APHU6175131 APA9896250

APHU6181406 APA9916515
 
well main frame will import from excel... if the path i given with input location such as given in the attached excel file...
 
You must log in or register to reply here.
Back
Top