Solved: Getting rid of the glimpse of opening/closing file

[GreenTree]

Good evening,

I'm working on a project where spreadsheet #1 is open, and the VBA code there opens another spreadsheet, reads some values from it, and closes it. The goal is that the second spreadsheet is never seen, but that doesn't seem to be happening.

I have application.screenupdating = false before the second workbook is opened, but that doesn't prevent the glimpse of the spreadsheet. From searching the forums, I tried workbook().visible = false but all that did was foul up my attempts to read data from it without stopping me from seeing it (briefly). Closing the VBA editor didn't change anything. Putting the screenupdating command earlier or later in the code doesn't seem to change anything.

The read from the second spreadsheet is very quick, so a splash screen would be just as distracting.

Anybody seen this sort of thing before? Any thoughts?

Many thanks,

G.T. :banghead:

[gnod]

you can use this function to read the data from closed workbook..


'Read data in a closed workbook
Private Function ReadDataFromWorkbook(SourceFile As String, SourceRange As Variant) As Variant
Dim cnn As ADODB.Connection, rst As ADODB.Recordset
Dim strConnection As String, strMyPrompt As String

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & SourceFile & ";" & _
"Extended Properties=""Excel 8.0;HDR=No"";"

Set cnn = New ADODB.Connection
On Error GoTo ErrorHandler
cnn.Open strConnection
Set rst = cnn.Execute("[" & SourceRange & "]")
On Error GoTo 0
ReadDataFromWorkbook = rst.GetRows 'returns a two dim array with all records in rst
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
On Error GoTo 0
Exit Function

ErrorHandler:
strMyPrompt = "The Source File or Source Range is invalid!"

MsgBox strMyPrompt, vbCritical, "Error: Consolidation"
Set rst = Nothing
Set cnn = Nothing
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With

'Terminates execution
End
End Function

[lucas]

GT,
Could you post the code your using please?

[GreenTree]

Wow! I hadn't ever played with that sort of method before. I'll give it a try. For what I'm doing right now, I'm only reading a few values, and that seems like it should work well. Later, I'll have some spreadsheets so large I'll HAVE TO open them. Does the "application.screenupdating = false" trick only work for screen updates within the SAME spreadsheet? Is there a way to open but not see another spreadsheet?

many thanks,

G.T.

[unmarkedhelicopter]

I did an application a while ago where a master sheet held data updated in several subordinate supervisor workbooks, following update each supervisor book needed to be opened, updated and closed. I used a form to show progress :- Updating Book 3 of 17 etc.
No flashes of the subordinate books were shown and I used the method you say you are using, as lucas says, post your book and we'll have a look. (We'll probably need a subordinate file too).

[lucas]

GT,
Here is a post you can check against the method you are using.....it might help..don't know.
http://www.vbaexpress.com/forum/showpost.php?p=104442&postcount=17

[GreenTree]

:blush:blush:blush

Okay, my foolishness, and apologies for wasting everybody's time. This project is my first time using the full screen & custom buttons bar, and what I didn't catch until I started stripping the code down to post as Lucas suggested, is that every time the main worksheet activates it goes to full screen etc, and each time that it loses activation, it reverts to normal screen, WHICH TURNS OFF application.screenupdating ! DOH!

When I put in a global variable to let the routine that loads the new file tell the screen-restore routines to take the day off, everything works exactly as expected. My apologies again for wasting everybody's time, and thanks to gnod for a cool-looking technique that I may end up using "just 'cause {now} I can." And to Lucas for setting me on the path to finding the error of my ways!

:oops:

G.T.

[johnske]

If you're going to read from a closed book, here's another way...
Option Explicit

Sub ExtractDataFromClosedBook1()

Application.ScreenUpdating = False

'first we'll create links to get the data from the closed book
'(put your own path and ranges below)
With [Sheet1!A1:H400]
' (------------worksheet links to the closed book-----------)
.Value = "='" & ActiveWorkbook.Path & "\[Book11.xls]Sheet1'!A1:H400"

'now remove the links
.Value = .Value
End With

Application.ScreenUpdating = True

End Sub

[GreenTree]

Wow! That is extremely cool! Stuff even I can understand, elegant without being as cosmic as the ADODB stuff.

What are the advantages do doing it this way instead of opening the new spreadsheet (now that my flashing spreadsheet self-inflicted glitch is resolved)? I'm sure there are several, and I'm not real sure I'd pick up on them all.

Many thanks,

G.T.

[johnske]

There may be a little speed and memory advantage in particular if you're only wanting to extract the data from a few cells, but IMO you're generally better off just doing it the conventional way (opening the book) :)