Desperate I know I asked before

[jenkinspat]

Looking for the excel god that can tell me Im an ideot and that this
isn't as hard as I'm making it. If its possiable at all. Ill try to
explain.
I work in Oil Exploration and we have whats called a recorder that
tests our euipment on the ground and produces test results every time a
test is run. When the tests are done it creates an excel workbook with
the results. The generated workbook layout is exactly the same, the
only difference is the file name that is created when produced. What i
do is keep these workbooks and extract certain data out of them and
compile a tracking log in another workbook. The only problem is that my
kindergarden code requires me to rename the file before i can run it.

My goal is:
To achive the generated report in a certain folder leaving the
generated name the same but be able to open any given file and run the
clean up and extract code without the user having to rename it all the
time.
I know i babbling but this has been a thorn in by kaboos for months.
ANY idea or alternates would be worth a zillion cyber dollars LOL.

Current code is----
Im using simple Excel code in a Module attached to a button

-----
Sub ExtractLatData()
'
'

Sheets("stby").Select
Application.ScreenUpdating = False
ChDir "C:\Pt23 Spread"
Workbooks.Open Filename:="C:\Pt23 Spread\sensor.xls"
Sheets("RSR Summary").Select
Rows("1:14").Select
Selection.Delete Shift:=xlUp
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("A:L").Select
Selection.Copy
Application.DisplayAlerts = False

ActiveWindow.Close
Sheets("RSR Report Data (Errors)").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
ActiveWorkbook.Save
Sheets("Control").Select
Application.ScreenUpdating = True
MsgBox " Finished, Exit EXCEL and open the ACCESS file to view Report
"
End Sub
-------
The sensor file name is named different every day and the user has to
change the name before running the code. Of course half of them forget
to. The file contents of the file are the same except the changes for
the test data so it just the (set) name im trying to make easier.
Or maby a code that will change the name for them, maby.

Thank you............
Pat

 

[Nick Hodge]

Pat

You should be able to just get the workbook name like so

Sub ExtractLatData()
'
'
Dim sWbName as String

Sheets("stby").Select
Application.ScreenUpdating = False

sWbName = Application.GetOpenFileName()
Workbooks.Open Filename:=sWbName

Sheets("RSR Summary").Select

....Rest of code

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)