I've been having a problem using the getopenfilename function and would be very grateful if someone could help me please, here's the situation:
I have two workbooks:
Summary.xlsx - a comparison of day-to-day sales based on data pulled from import.xls
Import.xls - an automatically generated report from our system
I want to be able to click a button and the user will get a browse for file dialog box, they then select the file (Import.xls) and excel runs an index/match formula to pull the relevant data from the selected file and puts it in the selected (At the moment you need to have the top cell in the colum selected) column.
I currently have the following:
When I run this I get the first browse box as expected, select the file but then get another browse box with the title "Update Values:Import.xls" After selecting the file again I get the following formula in the cells:
=INDEX(F:F:\Users\Adam\Desktop\day-to-day 'C:\Users\Adam\Desktop\day-to-day test\test\[Import.xlsx]Import'!$K:$K,MATCH($B3,F:F:\Users\Adam\Desktop\day-to-day 'C:\Users\Adam\Desktop\day-to-day test\test\[Import.xlsx]Import'!$B:$B,0))
What I want is:
=INDEX('C:\Users\Adam\Desktop\day-to-day test\test\[Import.xlsx]Import'!$K:$K,MATCH($B3,'C:\Users\Adam\Desktop\day-to-day test\test\[Import.xlsx]Import'!$B:$B,0))
A few notes:
The above formula works correctly
If I remove the = from the start of the formula and print the rest as a string it is correct
The way I select the range works but I don't think it's the most efficient way, as I've only started working with VBA yesterday I'm not very familiar with all of it's functions!
I have two workbooks:
Summary.xlsx - a comparison of day-to-day sales based on data pulled from import.xls
Code:
Summary
Product code 1-Nov 2-Nov 3-Nov 4-Nov
a 001 34 54 45
d 004 55 54 76
e 005 82 99 87
Code:
November 4th Data
Col A Col B Col K
Product code ... Sales
a 001 ... 44
b 002 ... 74
c 003 ... 21
d 004 ... 73
e 005 ... 86
f 006 ... 74
I currently have the following:
Code:
Private Sub Import_Click()
Dim importFile As Variant
Dim importRange As String
Dim formula As String
importFile = Application.GetOpenFilename(Title:="Please Select a File", MultiSelect:=False)
importRange = Cells(ActiveCell.Row, ActiveCell.Column).Address(False, False) & ":" & Cells(Application.WorksheetFunction.CountA(ActiveSheet.Range("B2:B10000")) + 1, ActiveCell.Column).Address(False, False)
formula = "=INDEX(" & importFile & "!C11,MATCH(RC2," & importFile & "!C2,0))"
ActiveSheet.Range(importRange).FormulaR1C1 = formula
ActiveSheet.Range(importRange).Select
Selection.Value = Selection.Value
End Sub
When I run this I get the first browse box as expected, select the file but then get another browse box with the title "Update Values:Import.xls" After selecting the file again I get the following formula in the cells:
=INDEX(F:F:\Users\Adam\Desktop\day-to-day 'C:\Users\Adam\Desktop\day-to-day test\test\[Import.xlsx]Import'!$K:$K,MATCH($B3,F:F:\Users\Adam\Desktop\day-to-day 'C:\Users\Adam\Desktop\day-to-day test\test\[Import.xlsx]Import'!$B:$B,0))
What I want is:
=INDEX('C:\Users\Adam\Desktop\day-to-day test\test\[Import.xlsx]Import'!$K:$K,MATCH($B3,'C:\Users\Adam\Desktop\day-to-day test\test\[Import.xlsx]Import'!$B:$B,0))
A few notes:
The above formula works correctly
If I remove the = from the start of the formula and print the rest as a string it is correct
The way I select the range works but I don't think it's the most efficient way, as I've only started working with VBA yesterday I'm not very familiar with all of it's functions!