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.Applic ation")
Set fso = CreateObject("Scripting.Fi leSystemOb ject")
objExcel.Visible = False
filePath = BrowseForFile()
If filePath = "" Then
MsgBox "You have not selected the file", vbcritical
Else
Set objWorkbookv = objExcel.Workbooks.Open(fi lePath)
Set objWorkbookm =
objExcel.Workbooks.Open("C :\Users\Ar vind\Deskt op\test\Ma ster_Copy. xls")
For Each ws In objWorkbookv.Sheets
ws.Copy objWorkbookm.Sheets(objWor kbookm.She ets.Count)
Next
objWorkbookv.Close False
filepath = left(filepath,instr(1,file path,".xls ")-1)
objWorkbookm.SaveAs (filepath & "_" & timeStamp & ".xls")
objWorkbookm.Close SaveChanges = True
Set objworkbookd = objExcel.Workbooks.Open(fi lepath & "_" & timeStamp & ".xls")
objExcel.Visible = True
objworkbookd.sheets(1).Sel ect
objworkbookd.sheets(1).act ivate
with objExcel
.DisplayAlerts = True
.Quit
End With
Set objExcel = Nothing
Msgbox "you can start copying the coords"
End If
Function BrowseForFile()
BrowseForFile = CreateObject("WScript.Shel l").Exec( _
"mshta.exe ""about:<input type=file id=f>" & _
"<script>resizeTo(0,0);f.c lick();new
ActiveXObject('Scripting.F ileSystemO bject')" & _
".GetStandardStream(1).Wri teLine(f.v alue);clos e();</scri pt>""" _
).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
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.Applic
Set fso = CreateObject("Scripting.Fi
objExcel.Visible = False
filePath = BrowseForFile()
If filePath = "" Then
MsgBox "You have not selected the file", vbcritical
Else
Set objWorkbookv = objExcel.Workbooks.Open(fi
Set objWorkbookm =
objExcel.Workbooks.Open("C
For Each ws In objWorkbookv.Sheets
ws.Copy objWorkbookm.Sheets(objWor
Next
objWorkbookv.Close False
filepath = left(filepath,instr(1,file
objWorkbookm.SaveAs (filepath & "_" & timeStamp & ".xls")
objWorkbookm.Close SaveChanges = True
Set objworkbookd = objExcel.Workbooks.Open(fi
objExcel.Visible = True
objworkbookd.sheets(1).Sel
objworkbookd.sheets(1).act
with objExcel
.DisplayAlerts = True
.Quit
End With
Set objExcel = Nothing
Msgbox "you can start copying the coords"
End If
Function BrowseForFile()
BrowseForFile = CreateObject("WScript.Shel
"mshta.exe ""about:<input type=file id=f>" & _
"<script>resizeTo(0,0);f.c
ActiveXObject('Scripting.F
".GetStandardStream(1).Wri
).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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Solution provided