Make Excel File as ODBC Source using Microsoft Excel Driver (give the name as SumitODBC) Open a new excel file in which you will fetch the data from DB Data.xlsx.
Store data in your excel file and save it some location in your system, say DB Data.xlsx. Place a command button on your worksheet and add the following code lines. Read Excel WorkBook as DataBase follows the steps below. Remember, the Workbooks collection contains all the Workbook objects that are currently open. I can find and replace this easily enough, but I want the macro to select the file path that is generated in the active worksheet. The Close and Open Method in Excel VBA can be used to close and open workbooks.
My Workbooks all have similar names, except for month changes in the name. I would just like to know how do I make the macro select a file path that is generated on the active worksheet, for example in cell A1, instead of selecting it manually. If Not ws Is Nothing Then SheetExists = True Private Function SheetExists(sWSName As String) As Boolean MsgBox "There is no sheet with name :Raw_Data in:" & vbCr &. WsSht.Copy before:=sThisBk.Sheets("Sheet1") SImportFile = Application.GetOpenFilename( _įileFilter:="Microsoft Excel Workbooks, *.xls *.xlsx", Title:="Open Workbook")Ī Filename:=sImportFile This automates the complete process of data consolidation or summation. I have used a function for checking existence of a specific sheet.ĭim sImportFile As String, sFile As String We can consolidate data from multiple Excel workbooks using VBA. I have used a little different version for my requirements. Hello LeoLee, assign the following code for using import.