Excel VB code. Message pops up while code running asking question. Code must not wait.

**Heinrich Venter** · 09-28-2014, 04:50 AM

Hey guys.

I have a Excel VB code that looks at multiple sheets but it stops to ask me do I want to reopen the sheet etc. I need this code to run without asking me questions. What can I add into the code to force it to run without asking me questions?

I'm not sure if it will display the image I attached but it pops up after opening each file with the following: file name.xls is already open. Ropening will cause any changes you made to be discarded. Do you want to reopen file name.xls yes or no.

This is quite strange as I was running this code in Excel 2003 and it didn't give me problems but now I have 2013 pro version. It looks like it does it on the same file. I need to grab and info from different sections in each file and place them in rows next to each other. Please remember that this worked in an earlier version of excel.

I see now it asks me 5 times per file if I want to reopen the file with every loop it runs. Do you maybe have a section of code I can add to my current code to just go ahead and reopen?

This code looks thru hundreds of excel files and I cannot sit and click yes yes yes yes lol.

Please see code below.

Sub ABC()
Dim sPath As String, sName As String
Dim bk As Workbook, sh As Worksheet
Dim wshLoop As Worksheet
Dim rw As Long

Set sh = ActiveSheet  'I will record the value and workbook name
' in the activesheet when the macro runs

rw = 3 ' which row to write to in the activesheet
sPath = "C:\Users\Heinrich Venter\Documents\AWA Water Management\Trend Program\Trend files\"
sName = Dir(sPath & "*trend*.xls") ' for xl2007 & "*.xls"
Do While sName <> ""

Set bk = Workbooks.Open(sPath & sName)
For Each wshLoop In bk.Sheets
sh.Cells(rw, "A") = bk.Name ' File name
sh.Cells(rw, "B") = wshLoop.Range("I3")  ' Name
sh.Cells(rw, "C") = wshLoop.Range("N14") ' Date
sh.Cells(rw, "D") = wshLoop.Range("J7")  ' Client
sh.Cells(rw, "E") = wshLoop.Range("Q9")  ' Circuit
sh.Cells(rw, "F") = wshLoop.Range("Q10") ' Volume m3
sh.Cells(rw, "G") = wshLoop.Range("N16") ' SS
sh.Cells(rw, "H") = wshLoop.Range("N17") ' Con
sh.Cells(rw, "I") = wshLoop.Range("N18") ' pH
sh.Cells(rw, "J") = wshLoop.Range("N19") ' Soluble Iron
sh.Cells(rw, "K") = wshLoop.Range("N20") ' Total Iron
sh.Cells(rw, "L") = wshLoop.Range("N21") ' Mol
sh.Cells(rw, "M") = wshLoop.Range("N22") ' Nitrite
sh.Cells(rw, "N") = wshLoop.Range("N23") ' Glycol
sh.Cells(rw, "O") = wshLoop.Range("N24") ' Aerobic bacteria
sh.Cells(rw, "P") = wshLoop.Range("N25") ' Anaerobic bacteria
sh.Cells(rw, "Q") = wshLoop.Range("N27") ' Mild steel
sh.Cells(rw, "R") = wshLoop.Range("N29") ' Copper
sh.Cells(rw, "S") = wshLoop.Range("G43") ' Comment Ser 1
sh.Cells(rw, "T") = wshLoop.Range("F43") ' Check field
rw = rw + 1
Next wshLoop

Set bk = Workbooks.Open(sPath & sName)
For Each wshLoop In bk.Sheets
sh.Cells(rw, "A") = bk.Name ' File name
sh.Cells(rw, "B") = wshLoop.Range("I3")  ' Name
sh.Cells(rw, "C") = wshLoop.Range("O14") ' Date
sh.Cells(rw, "D") = wshLoop.Range("J7")  ' Client
sh.Cells(rw, "E") = wshLoop.Range("Q9")  ' Circuit
sh.Cells(rw, "F") = wshLoop.Range("Q10") ' Volume m3
sh.Cells(rw, "G") = wshLoop.Range("O16") ' SS
sh.Cells(rw, "H") = wshLoop.Range("O17") ' Con
sh.Cells(rw, "I") = wshLoop.Range("O18") ' pH
sh.Cells(rw, "J") = wshLoop.Range("O19") ' Soluble Iron
sh.Cells(rw, "K") = wshLoop.Range("O20") ' Total Iron
sh.Cells(rw, "L") = wshLoop.Range("O21") ' Mol
sh.Cells(rw, "M") = wshLoop.Range("O22") ' Nitrite
sh.Cells(rw, "N") = wshLoop.Range("O23") ' Glycol
sh.Cells(rw, "O") = wshLoop.Range("O24") ' Aerobic bacteria
sh.Cells(rw, "P") = wshLoop.Range("O25") ' Anaerobic bacteria
sh.Cells(rw, "Q") = wshLoop.Range("O27") ' Mild steel
sh.Cells(rw, "R") = wshLoop.Range("O29") ' Copper
sh.Cells(rw, "S") = wshLoop.Range("G44") ' Comment Ser 2
sh.Cells(rw, "T") = wshLoop.Range("F44") ' Check field
rw = rw + 1
Next wshLoop

Set bk = Workbooks.Open(sPath & sName)
For Each wshLoop In bk.Sheets
sh.Cells(rw, "A") = bk.Name ' File name
sh.Cells(rw, "B") = wshLoop.Range("I3")  ' Name
sh.Cells(rw, "C") = wshLoop.Range("P14") ' Date
sh.Cells(rw, "D") = wshLoop.Range("J7")  ' Client
sh.Cells(rw, "E") = wshLoop.Range("Q9")  ' Circuit
sh.Cells(rw, "F") = wshLoop.Range("Q10") ' Volume m3
sh.Cells(rw, "G") = wshLoop.Range("P16") ' SS
sh.Cells(rw, "H") = wshLoop.Range("P17") ' Con
sh.Cells(rw, "I") = wshLoop.Range("P18") ' pH
sh.Cells(rw, "J") = wshLoop.Range("P19") ' Soluble Iron
sh.Cells(rw, "K") = wshLoop.Range("P20") ' Total Iron
sh.Cells(rw, "L") = wshLoop.Range("P21") ' Mol
sh.Cells(rw, "M") = wshLoop.Range("P22") ' Nitrite
sh.Cells(rw, "N") = wshLoop.Range("P23") ' Glycol
sh.Cells(rw, "O") = wshLoop.Range("P24") ' Aerobic bacteria
sh.Cells(rw, "P") = wshLoop.Range("P25") ' Anaerobic bacteria
sh.Cells(rw, "Q") = wshLoop.Range("P27") ' Mild steel
sh.Cells(rw, "R") = wshLoop.Range("P29") ' Copper
sh.Cells(rw, "S") = wshLoop.Range("G45") ' Comment Ser 3
sh.Cells(rw, "T") = wshLoop.Range("F45") ' Check field
rw = rw + 1
Next wshLoop

Set bk = Workbooks.Open(sPath & sName)
For Each wshLoop In bk.Sheets
sh.Cells(rw, "A") = bk.Name ' File name
sh.Cells(rw, "B") = wshLoop.Range("I3")  ' Name
sh.Cells(rw, "C") = wshLoop.Range("Q14") ' Date
sh.Cells(rw, "D") = wshLoop.Range("J7")  ' Client
sh.Cells(rw, "E") = wshLoop.Range("Q9")  ' Circuit
sh.Cells(rw, "F") = wshLoop.Range("Q10") ' Volume m3
sh.Cells(rw, "G") = wshLoop.Range("Q16") ' SS
sh.Cells(rw, "H") = wshLoop.Range("Q17") ' Con
sh.Cells(rw, "I") = wshLoop.Range("Q18") ' pH
sh.Cells(rw, "J") = wshLoop.Range("Q19") ' Soluble Iron
sh.Cells(rw, "K") = wshLoop.Range("Q20") ' Total Iron
sh.Cells(rw, "L") = wshLoop.Range("Q21") ' Mol
sh.Cells(rw, "M") = wshLoop.Range("Q22") ' Nitrite
sh.Cells(rw, "N") = wshLoop.Range("Q23") ' Glycol
sh.Cells(rw, "O") = wshLoop.Range("Q24") ' Aerobic bacteria
sh.Cells(rw, "P") = wshLoop.Range("Q25") ' Anaerobic bacteria
sh.Cells(rw, "Q") = wshLoop.Range("Q27") ' Mild steel
sh.Cells(rw, "R") = wshLoop.Range("Q29") ' Copper
sh.Cells(rw, "S") = wshLoop.Range("G46") ' Comment Ser 4
sh.Cells(rw, "T") = wshLoop.Range("F46") ' Check field
rw = rw + 1
Next wshLoop

Set bk = Workbooks.Open(sPath & sName)
For Each wshLoop In bk.Sheets
sh.Cells(rw, "A") = bk.Name ' File name
sh.Cells(rw, "B") = wshLoop.Range("I3")  ' Name
sh.Cells(rw, "C") = wshLoop.Range("R14") ' Date
sh.Cells(rw, "D") = wshLoop.Range("J7")  ' Client
sh.Cells(rw, "E") = wshLoop.Range("Q9")  ' Circuit
sh.Cells(rw, "F") = wshLoop.Range("Q10") ' Volume m3
sh.Cells(rw, "G") = wshLoop.Range("R16") ' SS
sh.Cells(rw, "H") = wshLoop.Range("R17") ' Con
sh.Cells(rw, "I") = wshLoop.Range("R18") ' pH
sh.Cells(rw, "J") = wshLoop.Range("R19") ' Soluble Iron
sh.Cells(rw, "K") = wshLoop.Range("R20") ' Total Iron
sh.Cells(rw, "L") = wshLoop.Range("R21") ' Mol
sh.Cells(rw, "M") = wshLoop.Range("R22") ' Nitrite
sh.Cells(rw, "N") = wshLoop.Range("R23") ' Glycol
sh.Cells(rw, "O") = wshLoop.Range("R24") ' Aerobic bacteria
sh.Cells(rw, "P") = wshLoop.Range("R25") ' Anaerobic bacteria
sh.Cells(rw, "Q") = wshLoop.Range("R27") ' Mild steel
sh.Cells(rw, "R") = wshLoop.Range("R29") ' Copper
sh.Cells(rw, "S") = wshLoop.Range("G47") ' Comment Ser 5
sh.Cells(rw, "T") = wshLoop.Range("F47") ' Check field
rw = rw + 1
Next wshLoop

Set bk = Workbooks.Open(sPath & sName)
For Each wshLoop In bk.Sheets
sh.Cells(rw, "A") = bk.Name ' File name
sh.Cells(rw, "B") = wshLoop.Range("I3")  ' Name
sh.Cells(rw, "C") = wshLoop.Range("S14") ' Date
sh.Cells(rw, "D") = wshLoop.Range("J7")  ' Client
sh.Cells(rw, "E") = wshLoop.Range("Q9")  ' Circuit
sh.Cells(rw, "F") = wshLoop.Range("Q10") ' Volume m3
sh.Cells(rw, "G") = wshLoop.Range("S16") ' SS
sh.Cells(rw, "H") = wshLoop.Range("S17") ' Con
sh.Cells(rw, "I") = wshLoop.Range("S18") ' pH
sh.Cells(rw, "J") = wshLoop.Range("S19") ' Soluble Iron
sh.Cells(rw, "K") = wshLoop.Range("S20") ' Total Iron
sh.Cells(rw, "L") = wshLoop.Range("S21") ' Mol
sh.Cells(rw, "M") = wshLoop.Range("S22") ' Nitrite
sh.Cells(rw, "N") = wshLoop.Range("S23") ' Glycol
sh.Cells(rw, "O") = wshLoop.Range("S24") ' Aerobic bacteria
sh.Cells(rw, "P") = wshLoop.Range("S25") ' Anaerobic bacteria
sh.Cells(rw, "Q") = wshLoop.Range("S27") ' Mild steel
sh.Cells(rw, "R") = wshLoop.Range("S29") ' Copper
sh.Cells(rw, "S") = wshLoop.Range("G48") ' Comment Ser 6
sh.Cells(rw, "T") = wshLoop.Range("F48") ' Check field
rw = rw + 1
Next wshLoop

bk.Close SaveChanges:=False
sName = Dir()
Loop

End Sub

Thank you all for the help.