Open Workbook Macro???

[Supe]

I created a macro that copies the data from one workbook to another. This
only works if both workbooks are open. Is it possible to run this without
opening the workbook I am getting the data from? If I do need to open it
first, when to I need to add to the beginning of the macro to open this
workbook?

 

[Dave Peterson]

Dim Wkbk as workbook
dim myPathName as string
dim myFileName as string

mypathname = "C:\yourpath\to\yourfile\"
myfilename = "someworkbookname.xls"

set wkbk = nothing
on error resume next
set wkbk = workbooks(myfilename)
on error goto 0

if wkbk is nothing then
'it wasn't open, so try to open it
on error resume next
set wkbk = workbooks.open(filename:=mypathname & myfilename)
on error goto 0

if wkbk is nothing then
msgbox "That file couldn't be opened" & vblf & mypathname & myfilename
exit sub '???
end if
end if

'do something with wkbk
msgbox wkbk.worksheets(1).range("a1").value

====
Untested, uncompiled. Watch for typos!

 

[Ron de Bruin]

You can try this example that copy a sheet.
It open the workbook, copy and close the workbook

If it is possible that the workbook is already open you must add a test for that
If you need with that help post back

Sub test()
Dim Wb1 As Workbook
Dim Wb2 As Workbook
Application.ScreenUpdating = False
Set Wb1 = ActiveWorkbook
Set Wb2 = Workbooks.Open("C:\data\ron.xls")
Wb2.Sheets("Sheet1").copy _
after:=Wb1.Sheets(Wb1.Sheets.Count)
Wb2.Close False
Application.ScreenUpdating = True
End Sub