Link to home
Start Free TrialLog in
Avatar of Lokanath K
Lokanath K

asked on

Copy sheet from one workbook to another book without opening the excel using vbscript

Is there any possible to do the following execution using vbscript

1. Generally, we receive business data excel doc .xls or .xlsx (which doesn't have my code/macros in it)
2. I have created Master sheet (.xlsm) which has macros in it and no data.
3. Whenever i receive business data (1): i have to copy that into master sheet (2),SaveAs (.xls) as date stamp and run macros into the new one.
4. Master sheet should not be open while copying because i have wrote some line code in "BeforeClose" method in master sheet.
Please anyone help me out on this to achieve the above. Below is the my code
Dim objworkbookv
Dim objworkbookm
Dim objworkbookd
Dim ws
Set objExcel = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")
objExcel.Visible = False
filePath = BrowseForFile()
If filePath = "" Then
MsgBox "You have not selected the file", vbcritical
Else
Set objWorkbookv = objExcel.Workbooks.Open(filePath)
Set objWorkbookm =
objExcel.Workbooks.Open("C:\Users\Arvind\Desktop\test\Master_Copy.xls")
For Each ws In objWorkbookv.Sheets
    ws.Copy objWorkbookm.Sheets(objWorkbookm.Sheets.Count)
Next
objWorkbookv.Close False
filepath = left(filepath,instr(1,filepath,".xls")-1)
objWorkbookm.SaveAs (filepath & "_" & timeStamp & ".xls")
objWorkbookm.Close SaveChanges = True
Set objworkbookd = objExcel.Workbooks.Open(filepath & "_" & timeStamp &  ".xls")
objExcel.Visible = True
objworkbookd.sheets(1).Select
objworkbookd.sheets(1).activate
with objExcel
.DisplayAlerts = True
.Quit
End With
Set objExcel = Nothing
Msgbox "you can start copying the coords"
End If

Function BrowseForFile()
BrowseForFile = CreateObject("WScript.Shell").Exec( _
"mshta.exe ""about:<input type=file id=f>" & _
"<script>resizeTo(0,0);f.click();new
ActiveXObject('Scripting.FileSystemObject')" & _
".GetStandardStream(1).WriteLine(f.value);close();</script>""" _
).StdOut.ReadLine()
End Function

Function timeStamp()
Dim t
t = Now
timeStamp = Year(t) & "-" & _
Right("0" & Month(t),2)  & "-" & _
Right("0" & Day(t),2)  & "_" & _  
Right("0" & Hour(t),2) &  "_" & _
Right("0" & Minute(t),2) '    '& _    Right("0" & Second(t),2)
End Function
ASKER CERTIFIED SOLUTION
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Solution provided