• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code Success to get data from closed file but how to make fast?

Dear All,

I have a working code for get data from closed file..

Code:
'Category: VBA Functions | [Item URL]
''VBA does not include a method to retrieve a value from a closed file. You can, however, take advantage of Excel's ability to work with linked files.
''This tip contains a VBA function that retrieves a value from a closed workbook. It does by calling an XLM macro.
''Note: You cannot use this function in a worksheet formula.
''The GetValue Function
''The GetValue function, listed below takes four arguments:
'•path: The drive and path to the closed file (e.g., "d:\files")
'•file: The workbook name (e.g., "budget.xls")
'•sheet: The worksheet name (e.g., "Sheet1")
'•ref: The cell reference (e.g., "C4")
Private Function GetValue(Path, File, sheet, Ref)
'  Retrieves a value from a closed workbook
    Dim Arg As String
'  Make sure the file exists
    If Right(Path, 1) <> "\" Then Path = Path & "\"
    If Dir(Path & File) = "" Then
        GetValue = "File Not Found"
        Exit Function
    End If
'  Create the argument
    Arg = "'" & Path & "[" & File & "]" & sheet & "'!" & _
      range(Ref).range("A1").Address(, , xlR1C1)
'  Execute an XLM macro
    GetValue = ExecuteExcel4Macro(Arg)
End Function
'Using the GetValue Function

'To use this function, copy the listing to a VBA module. Then, call the function with the appropriate arguments.
'The Sub procedure below demonstrates. It simply displays the value in cell A1 in Sheet1 of a
'file named Budget.xls, located in the XLFiles\Budget directory on drive C:.
'Sub TestGetValue()
'Dim a As String
'  p = "C:\TEST\"
'    f = "SUITING-BUYER MASTER.xlsx"
'    s = "BUY MASTER"
'    a = "A1"
' MsgBox GetValue(p, f, s, a)
'End Sub

'Another example is shown below.
'This procedure reads 1,200 values (100 rows and 12 columns) from a closed file, and places the values into the active worksheet.

Sub TestGetValue2()
Dim p As String
Dim f As String
Dim s As String
Dim a As String

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


    p = "C:\"
    f = "SUITING-BUYER MASTER.xlsx"
    s = "Sheet1"
    a = "A1"
    Application.ScreenUpdating = False
    For r = 1 To 100
        For c = 1 To 5
            a = Cells(r, c).Address
            Cells(r, c) = GetValue(p, f, s, a)
        Next c
    Next r
    Application.ScreenUpdating = True
    Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

'Caveat
'In order for this function to work properly, a worksheet must be active in Excel.
'It will generate an error if all windows are hidden, or if the active sheet is a Chart sheet.

but this is very slow..

how to modify this code to run faster??

if use, how to use "Array", "Collection" or "dictionary" for make it fast?
or which from above 3 can do better? & how to use it?

hope little help ..will be appreciated..

Regards,
Chirag Raval
 
Dear sir.

please help which formula should be use? & how to use it in this code?


if fetching range then array, collection or dictionary is not useful ?

hope your co operation.


Regards,

Chirag Raval
 
Last edited:
For example read Collection VBA inner help (same for Dictionary object)
then tell me if there is any link with your purpose ?!

In your code, which part needs time ?

To access data from a closed workbook, see ADODB activeX
samples (SQL) within this forum and help on MDSN website …

For Excel basics formula, as always just do it manually !
First open the closed source workbook, edit a cell formula in the destination
workbook starting with = sign then activate source workbook and select
a cell, validate by Enter key, as explained in Excel inner help …
Close the source workbook, check formula.
So you can use it within your code via Formula property to load data,
Once loaded, your code can replace formula by value …
 
Dear Sir,

Code:
Sub Get_Data_Closed_buy_master()

Dim srpath As String
Dim srfile As String
Dim srsheet As String
Dim srrange As String

Dim myrange As String

srpath = "C:\BUYER MASTER\"
srfile = "SUITING-BUYER MASTER.xlsx"
srrange = ("A1:F300")

myrange = "srpath & srfile & srrange"
With ActiveSheet.range("A1:F300") = "myrange"
End With
End Sub

I also try as per your suggestion ..

Code:
With ActiveSheet.range("A1:F300") = "='C:\BUYER MASTER\[SUITING-BUYER MASTER.xlsx]BUY MASTER'!A1:F300"

but Error No 13 "Type Mismatch" displayed...

how to handle this..?


Regards,

Chirag Raval
 
Dear Sir,

I forgot to put sheet reference in above code...
I added as be low code .....

but same error 13 type mismatch.. displayed at

With ActiveSheet.range("A1:F300").Value = "myrange"


Code:
Sub Get_Data_Closed_buy_master()

Dim srpath As String
Dim srfile As String
Dim srsheet As String
Dim srrange As String

Dim myrange As String

srpath = "C:\BUYER MASTER\"
srfile = "SUITING-BUYER MASTER.xlsx"
srsheet = "BUY MASTER"
srrange = ("A1:F300")

myrange = "srpath & srfile & & srsheet & srrange"
With ActiveSheet.range("A1:F300").Value = "myrange"
'With ActiveSheet.range("A1:F300") = "='C:\BUYER MASTER\[SUITING-BUYER MASTER.xlsx]BUY MASTER'!A1:F300"
End With
End Sub

please do needful....

Regards,
Chirag Raval
 
Dear Narayan Sir,

Thanks for response...

I already establish variable myrange as range ....

but... code holt on ..

srrange = ("A1:F300")

"Run time error 91...object variable or with block variable not set" displayed

then

I may be assume that if I want to reference this range I must put whole
path from drive to range .like
srrange=(srpath & srfile & srsheet).range ("A1:F300") like this but ..

"Compile Error" expected end of statement...displayed..

I attached 2 files ...my code...file & from where I want to fetch data attached..


Hope I can near the solution...

Regards,

Chirag Raval
 

Attachments

  • COPY-BUYER MASTER FOR GET DATA FROM CLOSED FILE.xlsx
    129.9 KB · Views: 4
  • Get_ Data_From_Closed_File.xlsm
    14.5 KB · Views: 5
Dear sirs,

Can formula array will help ?..
If this can success, can be convert in
Dynemic range fetching?...

Unintentinaly this thread goes towards "Array"??

Hope there are solution available there..so I can use same solution on many required
Range fetching from various important closed files...

Regards,

Chirag Raval
 
Chirag R Raval
1) You uploaded two files.
If 'long-name-file' is Your Master and
other ... which is empty ... is it Your 'Source'-file?
What would You like to do with those files?
2) 'from Closed file' ...
Do You mean that You do not even want to open 'Source'-file?
or could You 'Source'-file open too?
3) What would be 'fast'?
 
Dear sir ,

As per required I attached.
Empty have module.to fetch data from "copy buyer master...."
If empty's module's code set to path of master ,code not fetching data from master...
(without open master)..as per above discussed errors displays.

Okay without open master....its data hard to fetch in empty file..then ..okay
Open it in background.

Regards,

Chirag Raval
 
Chirag R Raval ... hmm?
If Your code do not work as You would like, then I forget it!
Did You try to answer:
1) from which file and what You would like to copy and where?
3) What would be 'fast'?
 
Dear Sir,

This thread's core matter is "From Closed File"...
My 1st post's code already worked on closed file..
But it is two slow...may be for each cell, code
Each loop sequence hard coded access same file.
From bigining...that may be matter of why this code work slow..

But if you ask me that " which method is fast?.open itit?
Or without open?..then natural excel way is with open it..

(2) from file name copy buyer master..
To get data from closeend file...

Regards
Chirag Raval
 
Chirag R Raval
Why You do not use Excel's 'SaveCopyAs'?
or
Just OS's (depends which OS You use) copy and paste commands?
Those would be the most fastest methods to copy file to ... new/empty file.
... until You won't answer, how long time would be 'Your fast'.
 
Dear sirs,

Thanks for your response..
Mark l sir,,
(1) with formating needed (with formatting)
Because red and bold are differnt aspect by
Our firm...
(2)actually need dynamic.. (A1 to last row & A to last column )
I just try to obay as per you guideline as previous
But can not make it dynamic.. So if available dynemic will be
Best for me....
Actually.. If available this codw then it will be
Become very helpful as I can fetch data from many files as required..

Regards,

Chirag Raval
 
So you just have to Open the source workbook (no choice)
and Copy the desired range whatever by End property
or via UsedRange (see your previous threads) …
You can achieve that manually, just activate the Macro Recorder.

Is it so difficult to crystal clear explain your need with complete details
as every poster must within its initial demand ?! See forum rules …
Do not be surprised in later threads if you get fewer & fewer answers
without a good presentation.
When I have more than a couple of questions, I directly move to next thread …
 
Dear Respected Experts,

Thanks for your suggestion ...& efforts for this thread..

Forget everything except my first post of this thread..

I want in current active blank sheet ..from file "Copy Suting Buyer Master" 's Sheet 1's dynemic data
With all formatting preserved..that's it.

Oh ..really gone out of mind ..that macro recorder can provide
Row materials to achieve basi structure of code..
In overwhelming mind of vast VBA concepts
User can face out of mind from some basics..

I will revert what recorded & try to make it dynemic.. copy with formatting
If I stop some where in code..I will mention..

Thanks againto you all to point out what can I do also pointing
towards fastness depends on various aspects & situation for both.
.code side & data side..

Regards,

Chirag Raval
 
Last edited:
Back
Top