Show Workbook Location & Path 2
Microsoft Visual Basic 2005 Step by Step (0) 0%
Excel 2003 Power Programming with VBA (2) 100%
Excel VBA Macro Programming (0) 0%
EXCEL VBA FOR DUMMIES (0) 0%
Question: Is there a way to display the location of an active workbook on my local drive and save it into a varible. I'm new to Excel and VBA but I'm trying to create a template to use for multiple vehicles. When the macro is run...
1) the workbook will have to identify what Workbook and worksheet it is in and save it as a varible so that it can access it later. Each workbook is specific to a vehicle.
*(The reason is because I will need to refer to the address of the original workbook when pasting the copied data from the "OilChart" Workbook.)
2) Copy the vehicle identification # and save as a varible. (Dim vehicleNumber As Variant)
3) Open another workbook on the local drive (OilChart.xls)
4) Run the macro in the new workbook (OilChart.xls) to update the data
5) Search Column B in (OilChart.xls) for matches of the vehicleNumber, and for all rows that match, select and copy all rows and paste it back into the orig workbook (Vehicle Specific Workbook).
This is the code I have so far. Any help would be appreiciated greatly. Please remember I'm a newbie so be discriptive. Thanks!
- Private Sub btnFind_Click()
- Dim vehicleNumber As Variant
- Dim sourceLocation As String
- Dim sourceFileName As String
- Dim fileName As String
- Application.ScreenUpdating = False
- 'copying the vehicle number into memory ie.EFLV55555555555
- vehicleNumber = ActiveSheet.Range("A13").Value
- 'Opens location of OilChart.xls sheet and runs the embedded update macro
- sourceLocation = "C:\Documents and Settings\jf01800\My Documents\Field Test Reports\"
- sourceFileName = "OilChart.xls"
- fileName = (sourceLocation + sourceFileName)
- 'Open OilChart.xls and activate the Data Sheet
- Workbooks.Open fileName:=fileName
- Application.Run "OilChart.xls!UpdateData" 'Run the Get Info Macro
- End Sub
I have'nt figured out how to do step 5 yet so if anybody has any ideas I'd be open to them. I tried looking at Aaron Bloods Kickbutt VBA Find Function but I got a little lost on how his code would relate to my code. It also looks like it doesn't copy or paste anything to another workbook. Again any help would be appreciated.