script for excel commands in vbs 2

[jfdabiri]

hi,
i have this application that i'm trying to automate for users to prevent user mistakes. basically, an excel file is received from clients. (arp0411.xls)
1 - the files is opened.
2 - rows one to four are deleted.
3 - the user has to copy a header row from another excel file (always the same). (arp_header.xls)
4 - the copied row is pasted to first row of the original file.
5 - row 2 of the file is deleted.
6 - the data is saved.
i recorded a macro and this is what it was produced:

Sub Macro1()
    Rows("1:4").Select
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Selection.Copy
    Windows("arp0411.xls").Activate
    Rows("1:1").Select
    ActiveSheet.Paste
    Rows("2:2").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
End Sub

my question is, how do i translate this into vbscript code?
i'm familiar with opening xcl sheets (i think). but not with these macros in vbs.
thanks.

 

[PHV]

Something like this (typed, untested)?
strDir = "\path\to\dir\"
strNewFile = "arp" & Right("0" & Month(Date),2) & Right("0" & Day(Date),2) & ".xls"
Set XL = CreateObject("Excel.Application")
Set N = XL.Workbooks.Open(strDir & strNewFile)
Set H = XL.Workbooks.Open(strDir & "arp_header.xls")
H.ActiveSheet.Rows("1:1").Copy
With N.ActiveSheet
.Rows("1:4").Delete -4162 ' xlUp
.Rows("1:1").Paste
XL.CutCopyMode = False
.Rows("2:2").Delete -4162 ' xlUp
End With
H.Close
Set H = Nothing
N.Save
N.Close
Set N = Nothing
XL.Quit
Set XL = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jfdabiri]

phv,
thanks so much. i'm going to try it. one question, though. when you you close an excel file and want to disregard the changes, how would you code it in this listing. because i have to close arp_header and disregard changes in that header file.
thanks.

 

[PHV]

Which changes are supposed to be done in the header file ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jfdabiri]

nothing was done. it just keeps saying header changed with a dialog box with 3 buttons, yes, no, cancel. is there anyway to end this header file with a "no"?
i tried the code. i'm getting an error on this line:
.Rows("1:1").Paste
it says object doesn't support this property or method.

 

[PHV]

And this ?
strDir = "\path\to\dir\"
strNewFile = "arp" & Right("0" & Month(Date),2) & Right("0" & Day(Date),2) & ".xls"
Set XL = CreateObject("Excel.Application")
XL.Visible = True
Set N = XL.Workbooks.Open(strDir & strNewFile)
Set H = XL.Workbooks.Open(strDir & "arp_header.xls")
H.ActiveSheet.Rows("1:1").Copy
With N.ActiveSheet
.Rows("1:4").Delete -4162 ' xlUp
.Range("A1").Paste
XL.CutCopyMode = False
.Rows("2:2").Delete -4162 ' xlUp
End With
H.Saved = True
H.Close
Set H = Nothing
N.Save
N.Close
Set N = Nothing
XL.Quit
Set XL = Nothing


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jfdabiri]

same message as before. it doesn't like it.
i got this from a macro:
Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = false

objExcel.Workbooks.Open("C:\a_a\arup0410x.xls")
objExcel.Workbooks.Open("C:\a_a\arup_header.xls")

with objExcel
.Windows("arp0410.xls").Activate
.Rows("1:6").Select
.Selection.Delete
.Windows("arp_header.xls").Activate
.Rows("1:1").Select
.Selection.Copy
.Windows("arp0410.xls").Activate
.Rows("1:1").Select
.Selection.Insert
.Windows("arp_header.xls").Activate
.Workbooks.close
.quit
end with

but, i get 2 dialog boxes:
1 - do you want to save changes to arp0410.xls - which i want to do. (yes, no, cancel) (yes)
2 - a large amount of somthin was copied somewhere. do you want to save it? which i do not want to do. (no)

if i can get rid of these dialog boxes, i've got this solved.

 

[PHV]

Like this ?
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objWB1 = objExcel.Workbooks.Open("C:\a_a\arup0410x.xls")
Set objWB2 = objExcel.Workbooks.Open("C:\a_a\arup_header.xls")
With objExcel
objWB1.Activate
.Rows("1:6").Select
.Selection.Delete
objWB2.Activate
.Rows("1:1").Select
.Selection.Copy
objWB1.Activate
.Rows("1:1").Select
.Selection.Insert
objWB2.Saved = True
objWB1.Save
.Workbooks.Close
.Quit
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[sparkbyte]

Maybe this will help.

'	Close Workbook and exit Excel
	objExcel.displayalerts=false
	objExcel.ActiveWorkbook.Close True
	objExcel.Quit

This is in a VBS script but you should be able to find the settings easy enough. if memory serves me all you would need to do is strip off the objExcell.



Thanks

John Fuhrman
Titan Global Services

http://www.titangs.com