Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read data from workbook without showing it?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read data from workbook without showing it?
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read data from workbook without showing it?
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 wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read data from workbook without showing it?
Hi,
Di it without opening the workbook. have a look here http://www.ozgrid.com/VBA/ExtractFromClosedWorkbook.htm Mike "Gustaf" wrote: 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 wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read data from workbook without showing it?
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 "Gustaf" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read data from workbook without showing it?
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 wrote: 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 "Gustaf" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Read data from workbook without showing it?
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 "Gustaf" wrote in message ... 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 wrote: 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 "Gustaf" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data showing on another workbook | Excel Discussion (Misc queries) | |||
Excel showing files as read only , when they aren't..... | Excel Worksheet Functions | |||
Showing data from one workbook in another. | Excel Discussion (Misc queries) | |||
read only not showing on open | Excel Discussion (Misc queries) | |||
remove read only - not showing up in properties | Excel Worksheet Functions |