Macro for data into and from Excel
Macro for data into and from Excel
(OP)
I'm trying to write a macro that will take 2 different strings from excel and input it into my screen, then pull the data the screen pulls up and put it in a spot in excel. I need this for a couple diff row and columns in excel. basically below is how the excel sheet will be formated, I will need the date put into my screen, then the id, so for example 3012009 then 1234, enter, some data pulls up for that date, it's just one field. i understand the rows/colums of the fields on the screen is what i need. But where i'm confused is how can i get name2 with their id and the date starting over again to input and pull the data i need. any help on this. Thx
REP name1 name2 name3
Date ID 1234 678 9876
3012009
3022009
3032009
3042009
3052009
3062009
REP name1 name2 name3
Date ID 1234 678 9876
3012009
3022009
3032009
3042009
3052009
3062009
RE: Macro for data into and from Excel
CODE
Dim Sessions, System As Object, Sess0 As Object, Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
file = "C:\test"
Dim obj as object
Dim objWorkbook as object
Set obj = CreateObject("Excel.Application")
obj.visible = True
obj.workbooks.open file
'---------------------------------
'assumption
'data begins in row 5, column a,c,d,e
'where column a is date
'column c,d,e are the names
'----------------------------------
rw = 5
col = 3
cola= 6
with obj.worksheets("ptw78")
for x = rw to obj.ActiveSheet.Rows.Count 'this will navigate
'column a with the dates
for y = col to 5 'this will navigate
'column c,d,e
MyDat = .cells(x,1)
MyNam = .cells(x,y)
If MyDat = "" Then Exit Sub
'msgbox MyDat
'msgbox MyNam
'-----send data to Attachmate-------
Sess0.Screen.PutString MyDat,2,4 'not sure where
Sess0.Screen.PutString MyNam,6,4 'you're placing this
Sess0.Screen.Sendkeys("<enter>")
'-----grab data from Attachmate-----
ExtraDat = Sess0.Screen.GetString (24,2,18) 'not sure where you're
'getting your data
'-----and place data in Excel-------
cola = cola + 1
.cells(x,cola) = ExtraDat 'this places the information in the same sheet
'column g,h,i
'----------------------------
next y 'next column
cola=6 'this brings the data back to column g,h,i
'for data input
next x 'next row
end with
End Sub
RE: Macro for data into and from Excel
CODE
MyNam = .cells(4,y) 'with this 'name are in row 4
sorry for the confusion
RE: Macro for data into and from Excel
col = 3
cola= 6
to how many i need?
RE: Macro for data into and from Excel
CODE
for y = 3 to 5
CODE
endcol = 10 'decide which column to end
'endcol = 10 means column "j"
for y = begcol to endcol
i used
CODE
cola = cola +1
if you want the data in the same columns as your sheet then replace
CODE
cola = cola + 1
.cells(x,cola) = ExtraDat
CODE
hth
RE: Macro for data into and from Excel
Illegal redefinition of 'Set'
RE: Macro for data into and from Excel
Excel Tip:
To get the Column Number, for the corresponding Column Letter...
CODE
Skip,
Don't let the Diatribe...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Macro for data into and from Excel
can you post your code?
RE: Macro for data into and from Excel
CODE
CODE
Dim Sessions, System As Object, Sess0 As Object, Set System = CreateObject("EXTRA.System")
Set Sessions = System.Sessions
Set Sess0 = System.ActiveSession
file = "H:\MonthlyNumbers.xls"
Dim obj as object
Dim objWorkbook as object
Set obj = CreateObject("Excel.Application")
obj.visible = True
obj.workbooks.open file
'---------------------------------
'assumption
'data begins in row 5, column a,c,d,e
'where column a is date
'column c,d,e are the names
'----------------------------------
rw = 5
col = 3
cola= 6
with obj.worksheets("ptw78")
for x = rw to obj.ActiveSheet.Rows.Count 'this will navigate
'column a with the dates
for y = col to 5 'this will navigate
'column c,d,e
MyDat = .cells(x,1)
MyNam = .cells(4,y) 'name are in row 4
If MyDat = "" Then Exit Sub
'msgbox MyDat
'msgbox MyNam
'-----send data to Attachmate-------
Sess0.Screen.PutString MyDat,4,25 'area data goes into
Sess0.Screen.PutString MyNam,5,13 'in citilink
Sess0.Screen.Sendkeys("<enter>")
'-----grab data from Attachmate-----
ExtraDat = Sess0.Screen.GetString (11,55,2) 'area getting data from
'-----and place data in Excel-------
cola = cola + 1
.cells(x,cola) = ExtraDat 'this places the information in the same sheet
'column g,h,i
'----------------------------
next y 'next column
cola=6 'this brings the data back to column g,h,i
'for data input
next x 'next row
end with
End Sub
RE: Macro for data into and from Excel
Have you looked in Extra HELP on
Skip,
Don't let the Diatribe...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Macro for data into and from Excel
RE: Macro for data into and from Excel
"ptw78" is a LITERAL not a VARIABLE.
It was vzachin's way of saying YOUR WORKSHEET since your handle is ptw78.
Skip,
Don't let the Diatribe...![[tongue] tongue](https://www.tipmaster.com/images/tongue.gif)
Just traded in my old subtlety...
talk you to death!
for a NUANCE!
RE: Macro for data into and from Excel
CODE
i don't see how you would get this error.
silly question: are you using attachmate extra?
you will be a programmer by the time you understand all this
RE: Macro for data into and from Excel