Open existing saved file in background for just reference of data & closed after work without ask anything

[chirag050675]

Dear All,

SUBJECT--OPEN EXISTING SAVED FILE IN BACKGROUND FOR JUST REFERENCE OF DATA & CLOSED AFTER WORK WITHOUT ASK ANYTHING.-JUST IT...

there are i know reference of Closed workbook (without open ) is some time not work....
if VBA have Silent power of this method...we not bear multi types of error...

we don't want try this... or try...that...just simple 100% working 100% successful method for , (1) open file in background & till stay this workbook (2) just pull data from invisible workbook (background workbook) (3) after complete work--background file closed without ask & save anything... just it....just simple.....

any way after

There are not found anywhere 100% perfect & 100% succeeded method for open existing file in background, for just data reference in current unsaved workbook & after work, closed -opened workbook in background without ask anything .

just simple question but it look impossible for Visual basic...

just want help for this ...

Thank you very much.

Regards,

Chirag

 

[Domenic]

It really depends on what you need to do.

If you simply need to retrieve a value from a closed workbook, you can use an XLM macro, which is an old style macro. The workbook is never opened, and so it remains closed at all times.

Otherwise, you simply turn off screen updating (ScreenUpdating = False) at the beginning of your code, open the workbook, do what needs to be done, close the workbook (without saving, if that's what you want), and turn screen updating back on (ScreenUpdating = True) at the end of your code. That's it. The workbook being opened/closed will not be seen.

If you need further help, try providing additional information, and someone will likely be able to provide you with a solution.

 

[TonyUK72]

An alternative option, would be to open the workbook that you require the data from, and copy this information into a temp sheet in your active workbook. When you have finished with your active workbook you can simply delete the temporary sheet you have used to harvest info from.

 

[chirag050675]

Dear Sir,

okay i am ready as per your suggestion but how???

i am not very familiar from visual basic for application.
can you provide me some example code.??

start.
open workbook in screen updating off

some work..in current workbook.like vlookup from closed workbook..
and close hidden workbook

close this macro..

the end.


please also provide XML techniques so i can also try that
as another option..

if you can help via both technique,
you can save me from this trouble.

waiting for favorable reply.

Rgards

Chirag

 

[Domenic]

To retrieve one or more values from a closed workbook using an XLM macro, have a look here...

Excel Tips From John Walkenbach: A VBA Function To Get A Value From A Closed File

Here's an example where ScreenUpdating is turned off, the workbook opened, etc...

[COLOR=green]'Force explicit declaration of variables[/COLOR]
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]

[COLOR=darkblue]Sub[/COLOR] test()

    [COLOR=green]'Declare variables[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sPath [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFile [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] sFullName [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
    [COLOR=darkblue]Dim[/COLOR] wbSource [COLOR=darkblue]As[/COLOR] Workbook
    [COLOR=darkblue]Dim[/COLOR] bWorkbookOpened [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Boolean[/COLOR]
    
    [COLOR=green]'Specify path to source file (change accordingly)[/COLOR]
    sPath = "C:\Users\Domenic\Desktop\"
    
    [COLOR=green]'Check if path exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] Len(Dir(sPath, vbDirectory)) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Path does not exist.", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]Else[/COLOR]
        [COLOR=green]'Make sure path ends in back slash[/COLOR]
        [COLOR=darkblue]If[/COLOR] Right(sPath, 1) <> "\" [COLOR=darkblue]Then[/COLOR]
            sPath = sPath & "\"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=green]'Specify source file (change accordingly)[/COLOR]
    sFile = "Book2.xlsx"
    
    [COLOR=green]'Specify path and source file[/COLOR]
    sFullName = sPath & sFile
    
    [COLOR=green]'Check if workbook exists[/COLOR]
    [COLOR=darkblue]If[/COLOR] Len(Dir(sFullName, vbNormal)) = 0 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Workbook does not exist.", vbInformation
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        
    [COLOR=green]'Turn off screen updating[/COLOR]
    Application.ScreenUpdating = [COLOR=darkblue]False[/COLOR]
    
    [COLOR=green]'Turn on error handling[/COLOR]
    [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] ErrHandler
    
    [COLOR=green]'Open specified workbook as read only[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wbSource = Workbooks.Open(Filename:=sFullName, ReadOnly:=True)
    bWorkbookOpened = [COLOR=darkblue]True[/COLOR]
    
    [COLOR=green]'Do stuff[/COLOR]
    '
    [COLOR=green]'[/COLOR]
    
ExitSub:
    [COLOR=green]'If source workbook was opened, close without saving[/COLOR]
    [COLOR=darkblue]If[/COLOR] bWorkbookOpened [COLOR=darkblue]Then[/COLOR]
        wbSource.Close SaveChanges:=[COLOR=darkblue]False[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

    [COLOR=green]'Turn screen updating back on[/COLOR]
    Application.ScreenUpdating = True
    
    [COLOR=green]'Clear from memory[/COLOR]
    [COLOR=darkblue]Set[/COLOR] wbSource = [COLOR=darkblue]Nothing[/COLOR]
    
    [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
ErrHandler:
    MsgBox "Error " & Err.Number & ":  " & Err.Description, vbCritical, "Error"
    [COLOR=darkblue]Resume[/COLOR] ExitSub
    
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[chirag050675]

Dear Sir,

Thank you very much for your reply.
& effort for others without any wishes.
i appreciate your goods work.

i will be hard work on your lesson &
reply with i am pass this successfully or not..
till then again thanks

Regards,
Chirag Raval