PDA

View Full Version : Help with Reusing file name



fraudh8er
07-19-2016, 04:24 PM
Help

I am just learning VBA for work, I am trying to make some reporting less time consuming.

This is the code:


Dim GRR_GL_T22_1st_Cash As Variant

While (file <> "")
If InStr(file, "GL") > 0 Then
'error checking to make sure it finds the GL report
MsgBox "found " & file

GRR_GL_T22_1st_Cash = "='H:\Testing\[Gl Report.xlsm]SiteFAIStatus'!$M$17"

'link to worksheet
With ThisWorkbook.Worksheets(1).Range("H5") '<< change as required
.Formula = GRR_GL_T22_1st_Cash
'convert formula to text
.Value = .Value

Questions:
1. I have found the file that I want, via the while loop. How do I use the variable file instead of 'H:\Testing\[GL Report.xlsm]? That way I can change the file name with ease and not have to copy and paste the file name in several places? I am currently copying the same line to grab multiple data in one line
2. How do I grab field N17 from SiteFAIStatus to thisworkbook.worksheet(1).Range(“I5”) other than copying the whole code above again
3. I am trying to do a IF then else, to give a value of “n/a” when the value from GL Report is blank or O (Number zero) but it is not working.


1. VBA for Applications 7.1
2. What you want it to do: see above
3. Cell references, bookmark names, column letters, row numbers, worksheets, styles, whatever pertains to the information at hand
4. Error messages if any: None


Thanks in advance. You people are amazing. I have learned a lot just searching so far.

Vince