I am needing a bit of assistance with Excel VBA. I am switching back and forth from Excel and another application and copying, pasting into Excel from the other application. I already have that process down, but I am needing advice on how I can paste into whatever cell is currently active, tab right, and at the end of that row go down one row, then start back in column D. Actually, here is a list of the exact process I am needing to happen inside the Excel application:
D:D column) D column.Between all of these steps I have the majority of that code figured out that interacts with the other application. But I do have one question in regards to that as well -- In that application I am running this statement:
With ATC.ActiveSession (ATC is simply referencing the application's type library to interact with other application)
As opposed to me ending the With statement every time the applications switch back and forth copying and pasting, what would I need to do use as a with statement to use excel's library?
Example of what I don't want to happen:
Sub New_ATS()
Set ATC = GetObject(, "ATWin32.AccuTerm")
AppActivate "AccuTerm 2K2"
With ATC.ActiveSession
.InputMode = 1
.SetSelection 6, 15, 12, 15
.Copy
.InputMode = 0
End With
AppActivate "Microsoft Excel"
Selection.Paste '(not complete, part of question)
Selection.Offset 1, 0 'again, part of the question
AppActivate "AccuTerm 2K2"
With ATC.ActiveSession
.InputMode = 1
.SetSelection 14, 3, 20, 3
.Copy
.InputMode = 0
End With
AppActivate "Microsoft Excel"
' .... end of partial code (continues on)
End Sub
But instead, would like to 'chain' the With statements, but I don't know what statement I would use to point back to Excel's VBA. This is what I would like:
Sub New_ATS()
Set ATC = GetObject(, "ATWin32.AccuTerm")
AppActivate "AccuTerm 2K2"
With ATC.ActiveSession
.InputMode = 1
.SetSelection 6, 15, 12, 15
.Copy
.InputMode = 0
With Excels_Statement '?????
AppActivate "Microsoft Excel"
Selection.Paste '(not complete, part of question)
Selection.Offset 1, 0 'again, part of the question
AppActivate "AccuTerm 2K2"
With ATC.ActiveSession
.InputMode = 1
.SetSelection 14, 3, 20, 3
.Copy
.InputMode = 0
With Excels_Statement '????
AppActivate "Microsoft Excel"
End With
End With
End With
End With
' .... end of partial code (continues on)
End Sub
I don't have AccuTerm installed but I think you can paste into Excel without having to activate it each time. Instead of using With blocks, you could assign an object variable with minimal typing... not the best practice for variable naming but it would do the trick. Declaring the variable of the specific type would give you access to Excel's library.
Here's what I'm thinking... (partially tested so you might have to tweak it a little)
Sub New_ATS()
Set ATC = GetObject(, "ATWin32.AccuTerm")
Dim Sesh as ATWin32.AccuTerm.Session 'Not sure this exists
Dim XL as Excel.Range
AppActivate "AccuTerm 2K2"
Set Sesh = ATC.ActiveSession
Sesh.InputMode = 1
Sesh.SetSelection 6, 15, 12, 15
Sesh.Copy
Sesh.InputMode = 0
'AppActivate "Microsoft Excel" - don't need it
Set XL = application.activecell
XL.PasteSpecial
Set XL = XL.offset(0,1)
'AppActivate "AccuTerm 2K2" - no need, still active
Sesh.InputMode = 1 'Once this is set, do you need to set it again?
Sesh.SetSelection 14, 3, 20, 3
Sesh.Copy
Sesh.InputMode = 0 'Once this is set, do you need to set it again?
XL.PasteSpecial
XL.NumberFormat = "General" 'Bullet #1
Set XL = XL.offset(1,0)
'...and so on...
XL.PasteSpecial
XL.NumberFormat = "d-mmm" 'Bullet #3
Set XL = XL.offset(1,0)
XL.PasteSpecial
XL.NumberFormat = "@" 'Bullet #5
Set XL = XL.offset(1,0)
XL.PasteSpecial
XL.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)" 'Bullet #7
Set XL = XL.offset(1,0)
XL = "X" 'Bullet #9
'When you've reached the end of the row
Set XL = XL.offset(1, 4 - XL.Column) 'Since col D = 4
'And repeat your procedure
End Sub