PDA

View Full Version : ExcelVBA - Opening files with certain conditions



MarcoPoloVB
02-23-2017, 08:15 AM
I have a problem with the VBA code.


I want to write a macro that will open the file named MonthNameConstant, where the name is taken from column A3 and xldown, and Month form B1.
With an open file will copy some data, and then paste to the table with the name.
Assuiming that, how to tell excel which exactly file should it choose to open and copy, and if there is no that file then go next.

I have right now something like this, Can this be dependent on variables?



Sub Data()


Dim wbSource As Workbook


Worksheets("Data").Activate
MsgBox ("Please choose files")
Filenames = Application.GetOpenFilename( _
Title:="Select a File to Import", _
MultiSelect:=True)
TotalWorkbooks = UBound(Filenames)

For I = 1 To TotalWorkbooks
Workbooks.Open FileName:=Filenames(I)
Set wbSource = ActiveWorkbook
wbSource.Sheets(1).Select
Range("B4:M4").Select
Selection.Copy

ThisWorkbook.Worksheets("Data").Cells(2 + I, 5).PasteSpecial _
Paste:=xlPasteValues

wbSource.Close SaveChanges:=False
Next I


End Sub

JBeaucaire
02-28-2017, 08:45 AM
This is the basic approach for what you've outlined, tweak from here:

Dim LR As Long, wbSOURCE As Workbook
Dim FNAME As String, fPATH As String, MyStr As String

fPATH = "C:\MyFiles\MySubfolder\TheFolder\" 'path to files, remember the final \ in this path string


With Sheets("Data") 'all the . commands connect to this sheet
LR = .Range("A3" & .Rows.Count).End(xlDown).Row 'note the last row
MyStr = .Range("A" & LR).Value 'construct the filename
MyStr = MyStr & .Range("B1").Value 'construct the filename

FNAME = Dir(fPATH & MyStr & "*") 'search the folder for files that starts with the constructed filename

If Len(FNAME) > 0 Then 'if the file is found then
Set wbSOURCE = Workbooks.Open(fPATH & FNAME) 'open the file
wbSOURCE.Sheets(1).Range("B4:M4").Copy 'copy from first sheet
.Cells(LR, 5).PasteSpecial Paste:=xlPasteValues 'paste as values
wbSOURCE.Close SaveChanges:=False 'close the opened file
Else 'if the file is not found give a report
MsgBox "The filename created was not found:" & vbLf & vbLf & fPATH & FNAME
End If
End With