View Full Version : help to fetch data from a closed file in excel
nileshsingar
05-11-2017, 05:23 AM
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
05-24-2017, 06:19 AM
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
05-24-2017, 06:21 AM
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
05-24-2017, 06:28 AM
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
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.