I have a workbook which retrieves data from another workbook via a button. I also want to automatically run this data retrieval process as soon as my workbook opens, so I copied the code into the "Open" property of "ThisWorkbook" in VBA.
The process takes the filepath of the current workbook and then foes up a directory and into another directory where the other workbook is. But when the code runs as part of the "Open" process, it is giving me a temporary filename and filepath i.e. "C:\Documents and Settings\.....\Temp" for the filepath and "CVT148.tmp" for the filename. But the workbook is open so it should work. I've tried using ThisWorkbook and Activeworkbook, but neither work.
Does anyone know how I can get around this?