VBA to extract data from file in use

[kevvukeka]

Hi All,

I have a macro which I use daily to extract data from multiple workbooks into one. Now everyday I need to request everyone to close their respective excel files before I run the macro. At times people are away from their desk and I need to wait till they come back.

Is there any way that I can include any line in my code that would extract data from excel file in use.

I tried the readonly:=True but it wont help.

Kindly suggest....

[Aussiebear]

Can you set a time to run the macro?

[kevvukeka]

Hi Aussiebear,

I can set the time, but the for that to happen all these excel sheets have to be moved to main sever(which needs permissions from top authorities in other countries). This may not be allowed.

[Kenneth Hobs]

Sub t()
MsgBox GetValue("x:\test", "test.xlsx", "Sheet1", "A1")
End Sub

'=GetValue("c:\files", "budget.xls", "Sheet1", "A1")
Private Function GetValue(path, file, sheet, ref)
' path = "d:\files"
' file = "budget.xls"
' sheet = "Sheet1"
' ref = "A1:R30"

Dim arg As String

If Right(path, 1) <> "\" Then path = path & "\"

If Dir(path & file) = "" Then
GetValue = "file not found"
Exit Function
End If

arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("a1").Address(, , xlR1C1)

GetValue = ExecuteExcel4Macro(arg)
End Function

[kevvukeka]

Hi Kenneth,

Sorry for my late reply. As a beginner I couldn't completely understand the above code. But is it gonna help me extract data from file in use? it would really save so much of time for me...

[Kenneth Hobs]

For my example, the file to get data from is x:\test\test.xlsx. It gets the data from Sheet named Sheet1 and its cell A1. It only gets the value, not cell formatting and such. If you have a path to the folder and security access, it should work fine.

The best way to learn is to just try it.