help to fetch data from a closed file in excel

[nileshsingar]

Friends
i need help to fetch data from a closed file
the function i use is vlookup
+VLOOKUP(A2,'D:\UPDATE\[PD190417.csv]PD190417'!$A$2:$H$25000,7,FALSE)
but it give error #n/a
can any body help to solve the same without opening the file?

[werafa]

You need to open the file first

you can use this to locate a file, and put the details into an excel sheet for later reference


Sub GetFilePath(myRow As Long)
'Return file name and path to worksheet cells


Dim myObject As Object
Dim fileSelected As String
Dim myPath As String
Dim myFile As String
Dim strLen As Integer


Set myObject = Application.FileDialog(msoFileDialogOpen)

With myObject
.Title = "Choose File"
.AllowMultiSelect = False
If .Show <> -1 Then Exit Sub
fileSelected = .SelectedItems(1)
End With

strLen = Len(fileSelected) - InStrRev(fileSelected, "\")
myFile = Right(fileSelected, strLen)
strLen = Len(fileSelected) - strLen - 1
myPath = Left(fileSelected, strLen)

With Worksheets("Admin")
.Range("D" & myRow) = myPath 'The file path
.Range("C" & myRow) = myFile 'The file name
.Range("C" & myRow, "D" & myRow).Font.Color = vbBlack
End With
End Sub


and here is an example of code I've used to open an external workbook, suck out some data, and close the external workbook



Sub UpdateData()
Attribute UpdateData.VB_ProcData.VB_Invoke_Func = " \n14"
' Updates the employee specific data table
' -> copies relevant tables from source data worksheet
' -> pastes values and formats


Dim myName As String 'Name of staff member/worksheet name
Dim myWB As Workbook 'this workbook


Call RunFast
Set myWB = ActiveWorkbook

myName = Trim(myWB.Sheets("Admin").Cells(1, 2).Value)
Call GetNewData(myName, myWB, "ChartData") 'load monthly data

myWB.Worksheets("Charts").Activate
myWB.Worksheets("Charts").Range("AB1").Value = Now 'set update
Call ResetApp
Exit Sub


QuitSub:
MsgBox ("Source data file not found. Please check name & location details on 'Admin' sheet")
Call ResetApp
End
End Sub


Sub GetNewData(wsName As String, myWBx As Workbook, dataSheet As String)
'ID the data range and copy it into the reporting workbook as numbers


Dim lastRow As Integer
Dim lastCol As Integer
Dim mysheet As Worksheet
Dim getSheet As Worksheet


Set mysheet = myWBx.Sheets(dataSheet)


If sheetExists(wsName) = True Then
Set getSheet = Worksheets(wsName)
mysheet.UsedRange.ClearContents
mysheet.Rows("1:1").UnMerge
getSheet.UsedRange.Copy
With mysheet.Range("A1")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With

Else
MsgBox ("Warning: Sheet named '" & wsName & "' not found")
End If
End Sub


Sub NewWBOpen(ByVal sString As String, wbName As String, ByRef bFlag1 As Boolean, ByRef bFlag2 As Boolean)
'Open the source workbook,
'-> bFlag 1: is workbook already open?
'-> bFlag2: was operation sucessful?


Dim myWBk As Workbook


On Error GoTo FilenameError
For Each myWBk In Workbooks
If myWBk.Name = wbName Then bFlag1 = True
Next
If bFlag1 = False Then
Call SilentOpen(False)
Workbooks.Open (sString)
Call SilentOpen(True)
End If
On Error GoTo 0
bFlag2 = True
Exit Sub


FilenameError:
MsgBox (sString & " not found. Please check name & location details on 'Admin' sheet")
On Error GoTo 0
bFlag2 = False
End
End Sub


Function GetPath(myWBx As Workbook) As String
'return workbook name and filepath as a string
'cell locations are 'hardwired'


Dim mWB As String, wWB As String 'monthly & weekly source data workbooks
Dim mWBPath As String, wWBPath As String 'monthly & weekly data workbook locations


mWBPath = Trim(myWBx.Sheets("Admin").Cells(5, 4).Value)
mWB = Trim(myWBx.Sheets("Admin").Cells(5, 2).Value)
GetPath = (mWBPath & "\" & mWB)

End Function

[werafa]

You will probably need to play with it a bit - and you may need to allow for the CSV file instead of native xlsx/xlsb, but this should give you an idea of how you can open, locate data, copy/paste and all that sort of thing.

werafa

[werafa]

Public Sub RunFast()
' Settings to speed macro execution
' must use ResetApp sub to reverse (using an error handler if necessary)
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = True
Application.StatusBar = "Stand by: Running Macros."
Application.ScreenUpdating = False
End Sub


Public Sub ResetApp()
' settings to reset working environment at end of code execution
' may need to run this maually in event of incomplete execution of code sequence
Application.Calculation = xlCalculationAutomatic
Application.StatusBar = ""
Application.ScreenUpdating = True
End Sub


Public Sub SilentOpen(myCmd As Boolean)
'supress "update links" and other on-open warings
Application.DisplayAlerts = myCmd
Application.AskToUpdateLinks = myCmd
End Sub


Public Sub SilentClose(myWB As Workbook)
'close wb without saving or showing any prompts
Application.DisplayAlerts = False
myWB.Saved = True
myWB.Close
Application.DisplayAlerts = True
End Sub