Read data from workbook without showing it?

[Gustaf]

How do you load a workbook from VBA, read data from selected sheets and then close it, without it ever showing?

Also, is it necessary to check whether a workbook is already opened before opening it, and closed before closing it?

Many thanks,

Gustaf

 

[Mike H]

Hi,

This opens a workbook and if it's alraedy open that doesn't cause a problem.
You would need to be more specific if you need help with reading data from
selected sheets

Sub OpenAndDothings()
'Change this to your directory
MyPath = "C:\"
MyFile = "Book3.xls"
Workbooks.Open Filename:=MyPath & MyFile
'Do things
ActiveWorkbook.Close savechanges:=False
End Sub

Mike

 

[Gustaf]

Thank you for the reply. The 'savechanges' parameter will be useful. However, I don't see the "without showing it" in your code. I'm currently using the Open method like you do and I'm able to import the data I want. But I'd rather not see the workbook at all (not as a window, and not on the task bar). Is that possible to achieve?

Gustaf

--

 

[Mike H]

Hi,

Di it without opening the workbook. have a look here

http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm

Mike

 

[Peter T]

If you mean open the workbook, process it and close it, before opening
disable screenupdating and reenable after closing it (or after processing
all workbooks)

application.screenupdating = false / true

If you mean you don't want to open the workbook at all look into ADO,
http://www.rondebruin.nl/ado.htm

If you mean something else explain.

Regards,
Peter T

 

[Don Guillett]

Sub OpenAndDothings()
'Change this to your directory
APPLICATION.SCREENUPDATING=FALSE
MyPath = "C:\"
MyFile = "Book3.xls"
Workbooks.Open Filename:=MyPath & MyFile
'Do things
ActiveWorkbook.Close savechanges:=False

APPLICATION.SCREENUPDATING=TRUE
End Sub

 

[Gustaf]

In my case, I need to loop through rows in the workbook to select what to import, while the examples I've seen with a closed workbook imports a known range. It's not a big deal whether the workbook shows or not, but it would have been nice to hide it from the user. Unfortunately the screen updating approach will also prohibit the user from making the selection while the workbook is open.

Many thanks,

Gustaf

--

 

[Peter T]

Now I'm really confused, how can a User make a selection in a workbook you
don't want hime to see. OK, I guess you don't mean that, if you mean open a
workbook keep it hidden, do stuff for a while and let user interact with the
interface, then close the hidden workbook, try something like this -

Sub test1()
Dim sName As String
Dim wb As Workbook
sName = "myFile.xls"
Application.ScreenUpdating = False
Set wb = Workbooks.Open(Filename:= _
"C:\<path>" & sName)
wb.Windows(1).Visible = False
wb.Saved = True
Application.ScreenUpdating = True
MsgBox wb.Name & " is open and hidden"

End Sub

Sub test2()
Dim bSaved As Boolean
Dim sName As String
Dim wb As Workbook
sName = "myfile.xls"

Set wb = Workbooks(sName)
bSaved = wb.Saved

If bSaved = False And wb.Windows(1).Visible = False Then
Application.ScreenUpdating = False
wb.Windows(1).Visible = True
Application.ScreenUpdating = True
wb.Close True ' save & close
Application.ScreenUpdating = True
Else
wb.Close False
End If

End Sub


If this still does not cover what you want, as I asked before, try and
explain otherwise it leaves everyone trying to second guess what you're
after.

Regards,
Peter T