Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a Workbook in the Background
I have a main "front end" workbook that people will be opening to interact
with. Whenever this main workbook opens, I would like it to open another "data" workbook in the BACKGROUND, so I can copy data back and forth between them using various scripts. I don't want the front end user to be able to see the data sheet, or (ideally) even know that it has been opened in the background. In the code for "ThisWorkbook" in the main front end workbook, I have: Dim objApp As Excel.Application Private Sub Workbook_Open() Set objApp = CreateObject("Excel.Application") objApp.Workbooks.Open ("X:\pathto\Data.xls") objApp.Visible = False End Sub In the code for the main worksheet in the main front end workbook, I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim DataSheet As Worksheet Set DataSheet = objApp.Workbooks("Data.xls").Sheets(1) [.....] End Sub So, with the code as I have shown above, I usually get an "Object Expected" error on my Set Datasheet line, which makes me think it isn't recognizing my public objApp variable that I declared in the code for ThisWorkbook. Should I be declaring it differently? If I simply remove the "objApp." prefix in that line, of course it says subscript out of range. If I use a regular Worksheets.Open (instead of creating a new app object), it brings the Data.xls workbook to the foreground and I can't figure out how to hide it.... I want the opening of the Data workbook to be stealthy and hidden, if that is at all possible.... Any help at all would be most appreciated. Thanks, ---G |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a Workbook in the Background
Enclosing your code with:
Application.Screenupdating = False [code] Application.Screenupdating = True This prevents the screen from being updated and the user will not see what is happening. This also speeds up the process. Prevent using activate or select within your code to run it faster and more efficiently Workbooks("A").Activate Sheets(1).Select Range("A1").Select Selection.Copy Workbooks("B").Activate Sheets(1).Select Range("A1").Select Selection.Paste is very messy. Use something like: Workbooks("B").Sheets(1).Range("A1") = Workbooks("A").Sheets(1).Range("A1") You can also append .Value, or .Formula, or ..... Than there are contructs using: With End With But with screenupdating turned off you can do all the selecting you want as long as you select your primary workbook at the end and the user won't see what is happing. rand451 "g-boy" wrote in message ... I have a main "front end" workbook that people will be opening to interact with. Whenever this main workbook opens, I would like it to open another "data" workbook in the BACKGROUND, so I can copy data back and forth between them using various scripts. I don't want the front end user to be able to see the data sheet, or (ideally) even know that it has been opened in the background. In the code for "ThisWorkbook" in the main front end workbook, I have: Dim objApp As Excel.Application Private Sub Workbook_Open() Set objApp = CreateObject("Excel.Application") objApp.Workbooks.Open ("X:\pathto\Data.xls") objApp.Visible = False End Sub In the code for the main worksheet in the main front end workbook, I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim DataSheet As Worksheet Set DataSheet = objApp.Workbooks("Data.xls").Sheets(1) [.....] End Sub So, with the code as I have shown above, I usually get an "Object Expected" error on my Set Datasheet line, which makes me think it isn't recognizing my public objApp variable that I declared in the code for ThisWorkbook. Should I be declaring it differently? If I simply remove the "objApp." prefix in that line, of course it says subscript out of range. If I use a regular Worksheets.Open (instead of creating a new app object), it brings the Data.xls workbook to the foreground and I can't figure out how to hide it.... I want the opening of the Data workbook to be stealthy and hidden, if that is at all possible.... Any help at all would be most appreciated. Thanks, ---G |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a Workbook in the Background
Dim acts much like Private. If you want the variable to be recognized by
another module, use Public: Public objApp As Excel.Application HTH, -- George Nicholson Remove 'Junk' from return address. "g-boy" wrote in message ... I have a main "front end" workbook that people will be opening to interact with. Whenever this main workbook opens, I would like it to open another "data" workbook in the BACKGROUND, so I can copy data back and forth between them using various scripts. I don't want the front end user to be able to see the data sheet, or (ideally) even know that it has been opened in the background. In the code for "ThisWorkbook" in the main front end workbook, I have: Dim objApp As Excel.Application Private Sub Workbook_Open() Set objApp = CreateObject("Excel.Application") objApp.Workbooks.Open ("X:\pathto\Data.xls") objApp.Visible = False End Sub In the code for the main worksheet in the main front end workbook, I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim DataSheet As Worksheet Set DataSheet = objApp.Workbooks("Data.xls").Sheets(1) [.....] End Sub So, with the code as I have shown above, I usually get an "Object Expected" error on my Set Datasheet line, which makes me think it isn't recognizing my public objApp variable that I declared in the code for ThisWorkbook. Should I be declaring it differently? If I simply remove the "objApp." prefix in that line, of course it says subscript out of range. If I use a regular Worksheets.Open (instead of creating a new app object), it brings the Data.xls workbook to the foreground and I can't figure out how to hide it.... I want the opening of the Data workbook to be stealthy and hidden, if that is at all possible.... Any help at all would be most appreciated. Thanks, ---G |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a Workbook in the Background
"STEVE BELL" wrote:
Enclosing your code with: Application.Screenupdating = False [code] Application.Screenupdating = True This prevents the screen from being updated and the user will not see what is happening. This also speeds up the process. Wow... this is interesting, and I didn't know about it. So thank you for the tip. However, in this particular case, I would like (if possible) to be able to LEAVE the workbook open in the background, so I can reference it "on demand" as it were with scripts, but I would like it to be invisible. The screenupdating thing doesn't fly for this, because I would like the user to be able to do things and interact with the "front end" workbook, while the data workbook is open (but hidden). Any thoughts? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a Workbook in the Background
Looks like you have declared objApp as a modular variable in the
ThisWorkbook module which will not be visible elsewhere in your project, such as your sheet modules. In a normal module try - Public objApp As Excel.Application To make things easier why not save your Data file as a hidden workbook and open in the normal way in same instance as your main workbook. Though of course user might see it in the Window Unhide. Regards, Peter T "g-boy" wrote in message ... I have a main "front end" workbook that people will be opening to interact with. Whenever this main workbook opens, I would like it to open another "data" workbook in the BACKGROUND, so I can copy data back and forth between them using various scripts. I don't want the front end user to be able to see the data sheet, or (ideally) even know that it has been opened in the background. In the code for "ThisWorkbook" in the main front end workbook, I have: Dim objApp As Excel.Application Private Sub Workbook_Open() Set objApp = CreateObject("Excel.Application") objApp.Workbooks.Open ("X:\pathto\Data.xls") objApp.Visible = False End Sub In the code for the main worksheet in the main front end workbook, I have: Private Sub Worksheet_Change(ByVal Target As Range) Dim DataSheet As Worksheet Set DataSheet = objApp.Workbooks("Data.xls").Sheets(1) [.....] End Sub So, with the code as I have shown above, I usually get an "Object Expected" error on my Set Datasheet line, which makes me think it isn't recognizing my public objApp variable that I declared in the code for ThisWorkbook. Should I be declaring it differently? If I simply remove the "objApp." prefix in that line, of course it says subscript out of range. If I use a regular Worksheets.Open (instead of creating a new app object), it brings the Data.xls workbook to the foreground and I can't figure out how to hide it.... I want the opening of the Data workbook to be stealthy and hidden, if that is at all possible.... Any help at all would be most appreciated. Thanks, ---G |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a Workbook in the Background
"George Nicholson" wrote:
Dim acts much like Private. If you want the variable to be recognized by another module, use Public: Public objApp As Excel.Application Thank you... I didn't know that. I figured "dim" was generic, with "Public" and "Private" as special cases. However, unfortunately *just* changing the declaration line to Public objApp As Excel.Application in the ThisWorkbook declaration didn't fix the problem... I still get an "Object Required" error on the line: Set DataSheet = objApp.Workbooks("Data.xls").Sheets(1) in the code for the worksheet. Could this be because one is declarated in the code for the workbook, but the subroutine that uses the variable is in the code for the worksheet? Do I need to do something to explicitly declare the thing as "global" or something? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Opening a Workbook in the Background
Another thing to keep in mind:
In your code you can hide and unhide any workbook. This way you can unhide the workbook and workwith it and than rehide it when finished. This way the user will never see it or have access to it. And you might be able to use xlVeryHidden to make sure that the user can't find it. -- rand451 "g-boy" wrote in message ... "George Nicholson" wrote: Dim acts much like Private. If you want the variable to be recognized by another module, use Public: Public objApp As Excel.Application Thank you... I didn't know that. I figured "dim" was generic, with "Public" and "Private" as special cases. However, unfortunately *just* changing the declaration line to Public objApp As Excel.Application in the ThisWorkbook declaration didn't fix the problem... I still get an "Object Required" error on the line: Set DataSheet = objApp.Workbooks("Data.xls").Sheets(1) in the code for the worksheet. Could this be because one is declarated in the code for the workbook, but the subroutine that uses the variable is in the code for the worksheet? Do I need to do something to explicitly declare the thing as "global" or something? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I stop blank workbook from opening when opening an existing | Excel Discussion (Misc queries) | |||
excel VBA problem - setting workbook as variable & opening/re-opening | Excel Programming | |||
How to make the opening of a workbook conditional upon the opening of another workbook | Excel Programming | |||
How to make opening of workbook conditional of opening of another workbook | Excel Programming | |||
Gray background is shown when loading a workbook | Excel Programming |