• 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.

VBA and Working with Open Workbooks

M

msquared99

Member
Here is the situation. I have a report that I run from an external database. The report will always have a different name due to when it is run (several times a day), 03-21-13 AM1. When it is run it automatically opens in Excel 2007. I then open the last report in a folder for which I have VBA code that does that part and works well. This report is Any AR 03-20-13PM2 and changes often as well but the macro is looking for the last file run which takes care of this.


What I am doing is adding some formulas to workbook 03-21-13 AM1 then doing a vlookup from the workbook 03-20-13PM2 which the macro opened into the workbook from the database, 03-21-13 AM1.


I have to make this an easy process because not very skilled people will be using this was well.


My issue is how do I get the macro to identify the workbook run from the database?


Here is the macro that finds the last file saved in the folder:

[pre]
Code: 
'Force the explicit delcaration of variables
Option Explicit

Sub OpenLatestFile()

'Declare the variables
Dim MyPath As String
Dim MyFile As String
Dim LatestFile As String
Dim LatestDate As Date
Dim LMD As Date
Dim path As Variant

path = InputBox(Prompt:="Enter the folder name. Must be MM.YYYY")

If path = 0 Then
MsgBox "No file name entered."
Exit Sub
End If

'Specify the path to the folder
MyPath = "B:AccountingDaily Reconciliation Reports3019-AR Report2013 3019 Reports" & path & ""

'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "" Then MyPath = MyPath & ""

'Get the first Excel file from the folder
MyFile = Dir(MyPath & "*.xls", vbNormal)

'If no files were found, exit the sub
If Len(MyFile) = 0 Then
MsgBox "No files were found...", vbExclamation
Exit Sub
End If

'Loop through each Excel file in the folder
Do While Len(MyFile) > 0

'Assign the date/time of the current file to a variable
LMD = FileDateTime(MyPath & MyFile)

'If the date/time of the current file is greater than the latest
'recorded date, assign its filename and date/time to variables
If LMD > LatestDate Then
LatestFile = MyFile
LatestDate = LMD
End If

'Get the next Excel file from the folder
MyFile = Dir

Loop

'Open the latest file
Workbooks.Open MyPath & LatestFile

End Sub
[/pre]

Thanks,


Mike
 
Msquared99


When posting Code please put a single ` (the backwards tick next to the 1 key, under the esc key) in front of and after your code

That forces Wordpress to honour the Formatting and maintain the indents

I've done it for you above.
 
Could you use the same macro structure, but when you're looping through the files, check to make sure that the Left(MyFile,2) <> "AR"? Otherwise, any more info you can give that would help us identify the report file would be good, such as file naming convention, location, etc.
 
OK I solved the problem I had above. I had the database just keep the file name the same when it exports to Excel and the user will change the file name when they save the file.


Now here is what I have:


Database File is open, the file name is "Any AR".


And the file the macro opened with the above code.


So how do I use both files that are open to do a VLOOKUP?


I want to pull data from the workbook the macro opened and put it in the database file.


Where you see lastRow1 and lastRow2 I put them in the code for a few formulas which work.


I tried this for the VLOOKUP but it does not work:


Any AR is the active workbook at this time.

[pre]
Code: 
Worksheets("Sheet1").Range("E3").Formula = "=vlookup(d4,choose({2,1},'[" & MyPath & LatestFile & "]sheet1'!$D$3:$D & lastrow1,'[" & MyPath & LatestFile & "]sheet1'!$h$3:$h & lastrow1),2,0)"
With Worksheets("Sheet1").Range("e3")
.AutoFill Destination:=Range("e3:e" & lastRow2)
End With
[/pre]
 
Just to be more clear:


Lookup_value = Any AR Cell E3


Table_array = CHOOSE({2,1},macro opened workbook sheet1 Range $D$3:lastRow1 and $H$3:lastRow1


Col_Index = 2


Range_lookup = 0
 
This line is incorrect

Code: 
.AutoFill Destination:=Range("e3:e" & lastRow2)

The [code]Range
on the right hand side doesn't have a parent object, so it's going to try to fill from the one workbook's E3 to active workbook's active sheet last row. Oops!

Try this:

.AutoFill Destination:= .Range("e3:e" & lastRow2)[/code]


Also, check the formula structure for workbooks. In a formula, it follows this structure:

='C:My Documents[My Books.xls]My Sheet'!A2


I think your code has both the file path and book name in the square brackets.
 
Thanks Luke!


I had to change a few other things but my issue is still with the VLOOKUP.


Worksheets("Sheet1") is the active workbook and Filename is open. The reason the macro uses Filename is because this workbook will always have a different name.


The part that seems to be the issue is " & , & " & Filename


Worksheets("Sheet1").Range("E3").Formula = "=vlookup(d4,choose({2,1}," & Filename.Sheet1.range("$D$3:$D & lastRow1")" & , & " & Filename.sheet1.range("$h$3:$h & lastRow1")",2,0)"


What should the syntax be?


Thanks!
 
VLOOKUP is still wrong syntax. The arrays your feeding, col D or col H, are both 1 column, but the column argument is 2. There is no 2nd column! I think this is what you are trying to do:

[pre]
Code: 
'Luke M: Somehow these 2 string variables are set
'Specify the path to the folder
MyPath = "B:AccountingDaily Reconciliation Reports3019-AR Report2013 3019 Reports" & path & ""

'Get the  file name
MyFile = "Cool stuff.xls"

With ActiveWorkbook.Worksheets("Sheet1")
.Range("E3").Formula = "=VLOOKUP(D4,CHOOSE({2,1},'" & MyPath & "[" & MyFile & "]Sheet1'!$D$3:$E" & lastrow1 & _
"'" & MyPath & "[" & MyFile & "]Sheet1'!$H$3:$I" & lastrow1 & ",2,0)"
.Range("E3").AutoFill Destination:=.Range("e3:e" & lastRow2)
End With
[/pre]
 
You must log in or register to reply here.
Back
Top