Question About Working Macro

[msquared99]

I have this macro that works but it asks for me to choose the file fName1 three or four times. Why?

Sub GetFile()

Dim fName1 As Variant, wbo As Workbook
Dim fName2 As Variant, wba As Workbook
Dim WBName As Worksheet


fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the prior spreadsheet:")

  If fName1 = False Then Exit Sub

  Set wbo = Workbooks.Open(fName1)
  wbo.Activate
 
  
  
fName2 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the current spreadsheet:")

  If fName2 = False Then Exit Sub

  Set wba = Workbooks.Open(fName2)
  
  wba.Activate
  
  Range("M2").Value = "1"
  Range("M2").Select
  Selection.AutoFill Destination:=Range("m2:m" & Cells(Rows.Count, "A").End(xlUp).Row)
  
  Range("N2").FormulaR1C1 = "=RC[-13]&RC[-10]"
  Range("N2").Select
  Selection.AutoFill Destination:=Range("N2:N" & Cells(Rows.Count, "A").End(xlUp).Row)
  
 
  Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],' & fName1 & '!R2C14:R7C14,1,0)"
  Range("O2").Select
  Selection.AutoFill Destination:=Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row)
  
 wbo.Close savechanges:=True
 wba.Close savechanges:=True

End Sub

 

[Chihiro]

This code alone only asks to enter sheet once. Each for previous and current.

Without seeing what other process/code are present in your workbook. It would be hard to tell where the issue is.

Upload sample demonstrating where your issue occurs.

 

[msquared99]

OK, here are the two files. SalesLimit1 will be the previous file or fName1 and SalesLimit2 will be the current file or fName2.

When the macro runs it asks: Update Values: & fName1 &

What the macro does is opens fName1 (SalesLimit1) and puts a VLOOKUP in column O of fName2 (SalesLimit2). The VLOOKUP looks funny but it w

 

[Chihiro]

Oh I see. It's because of this line.

 Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],' & fName1 & '!R2C14:R7C14,1,0)"

Try something like...

Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & wbo.Name & "'!R2C14:R7C14,1,0)"

 

[msquared99]

Thanks!

I still get the dialog box and have to choose fName1.



Thoughts?

 

[Chihiro]

You need to choose fName1 at least once. Since code uses GetOpenFilename to open the workbook.

If the file is fixed, you can just use hardcoded workbook name in it's place.

 

[msquared99]

I'm not sure I understand.

 

[Chihiro]

So the code, requires you to pick a file through dialog for it to open and assign it to a variable.

fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the prior spreadsheet:")

If fName1 is going to be consistently same file every time you run the code. Instead of using GetOpenFilename... Dim fName1 as string and hard code it. Replacing "C:\YourPath" with actual location of your file. Otherwise, use code as is, with modification indicated in post#4.

Dim fName1 as String

fName1 = "C:\YourPath\SalesLimit1-01262017-AM.xls"

 

[msquared99]

That's the issue, the file name and the sheet name will always be different names. That is why I chose GetOpenFileName.

Any suggestions?

 

[Chihiro]

Not sure I get you.

If you apply the modification in post #4. You just need to select each file once.

Sub GetFile()

Dim fName1 As Variant, wbo As Workbook
Dim fName2 As Variant, wba As Workbook
Dim WBName As Worksheet


fName1 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the prior spreadsheet:")

  If fName1 = False Then Exit Sub

  Set wbo = Workbooks.Open(fName1)
  wbo.Activate
 
 
fName2 = Application.GetOpenFilename(filefilter:="Excel Files (*.XLS), *.XLS", Title:="Select the current spreadsheet:")

  If fName2 = False Then Exit Sub

  Set wba = Workbooks.Open(fName2)
 
  wba.Activate
 
  Range("M2").Value = "1"
  Range("M2").Select
  Selection.AutoFill Destination:=Range("m2:m" & Cells(Rows.Count, "A").End(xlUp).Row)
 
  Range("N2").FormulaR1C1 = "=RC[-13]&RC[-10]"
  Range("N2").Select
  Selection.AutoFill Destination:=Range("N2:N" & Cells(Rows.Count, "A").End(xlUp).Row)
 
  Range("O2").FormulaR1C1 = "=VLOOKUP(RC[-1],'" & wbo.Name & "'!R2C14:R7C14,1,0)"
  Range("O2").Select
  Selection.AutoFill Destination:=Range("O2:O" & Cells(Rows.Count, "A").End(xlUp).Row)
 
wbo.Close savechanges:=True
wba.Close savechanges:=True

End Sub

What's the issue?

 

[msquared99]

The issue is, that once I have selected the files, I get a dialog box asking to Update Values from fName1.



How do I avoid that?

 

[Chihiro]

Where and how are you getting that?

I don't get that message.

 

[msquared99]

I get it right after I select the second file. I tried adding Application.DisplayAlerts but that messes up the formula.

I'm running Excel 2010.

It has me puzzled especially since you do not get that message.

 

[msquared99]

OK, there must have been something corrupt in the original macro. I copied the code from your post and put it in a new workbook and it works fine.