'File to save the generated insert statementsĭim StringStore As String 'Temporary variable to store partial statement MaxRow = InputBox("Give the Maximum Row No.") Row = InputBox("Give the starting Row No.") 'Inputs for the starting and ending point for the rows 'To store all the columns available in the current active sheetĭo Until ActiveSheet.Cells(Row, Col) = "" 'Loop until you find a blank.ĬolNames(ColCount) = "" The code can be further customized to generate insert statements for all the sheets given in an excel file. Insert into (, , )Ĭreating a macro to generate Insert statements from the excel file is very handy and easy than importing data from excel to SQL server or by package creation. So after giving the inputs to the script, it will generate the below code: insert into (, , ) Here the starting row no is 2 and maximum row no is 4. Let's take the example of a small table named Student and the data for this is: Name It will ask you for the range of data to be fetched like the starting Row no and the maximum Row no. Then it will fetch the values for the corresponding columns. The code is self descriptive in this I am fetching all the columns for the given active sheet at first. In the code window, enter the script given below:.In the Macro Dialog box, give a valid name for a macro you want to create, and press Create button.Here I too did the same thing, written one macro that will create INSERT statements for the current activated sheet. And the good thing is that we can write our own macro in VB and then we can execute it inside the Excel file. I want this task to be automated.Įxcel files includes a very good functionality called Macro execution. So that there will be no need to write Insert statements daily for each record. Here I want some easy method to populate those Excel data to my SQL server database. I am working on a project where I need to populate some configuration settings from an Excel file to the SQL server database so that on considering the values only my package will run.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |