Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
inserting data in an existing excel file from vb
- Thread starter kenguru
- Start date
NoCoolHandle
Programmer
You can easily do this with COM automation.
From memory the steps you should take.
1. Reference the Excel Type library (project -> references)
2. Declare a variable of excel.application
3. Create the excel.application object
4. Use the excel.application to open the excel file
5. Using the cells collection or the range object insert(append) your new data..
(if you wanted to do this on a new spreadsheet (Not workbook) - you would have needed to use the "activeworkbook" object and added a New spreadsheet to it...
(to find out what the code would look like in Excel (and you can cut and paste this into VB) just go to tools and "record a macro".. In the macro that you are recording open your xls file, do what you would like your vb app to do, stop recording your macro, then take a look at it.. at this point add the code to a vb project before step 4 and you are done...)
HTH
Rob
PS code might look something like...
this opens an existing file adds a new worksheet (before the current one (default)
in that new sheet it adds a value of test on row 1 col 3 and then saves it as something new before shutting down excel...
From memory the steps you should take.
1. Reference the Excel Type library (project -> references)
2. Declare a variable of excel.application
3. Create the excel.application object
4. Use the excel.application to open the excel file
5. Using the cells collection or the range object insert(append) your new data..
(if you wanted to do this on a new spreadsheet (Not workbook) - you would have needed to use the "activeworkbook" object and added a New spreadsheet to it...
(to find out what the code would look like in Excel (and you can cut and paste this into VB) just go to tools and "record a macro".. In the macro that you are recording open your xls file, do what you would like your vb app to do, stop recording your macro, then take a look at it.. at this point add the code to a vb project before step 4 and you are done...)
HTH
Rob
PS code might look something like...
this opens an existing file adds a new worksheet (before the current one (default)
in that new sheet it adds a value of test on row 1 col 3 and then saves it as something new before shutting down excel...
Code:
Dim x As Excel.Application
Set x = New Excel.Application
x.Workbooks.Open "C:\Jeremy.xls"
x.Worksheets.Add
x.Cells(1, 3) = "test"
x.ActiveWorkbook.SaveAs "C:\NewJeremy.xls"
x.Quit
Set x = Nothing- Thread starter
- #3
I've found that I can answer my own question if I use this syntax:
Dim myExcelWorkBook As New Excel.Workbook
Dim myExcelAddWorkSheet As Excel.Worksheet
.
.
.
Set myExcelAddWorkSheet = myExcelWorkBook.Worksheets.Add
myExcelAddWorkSheet.Name = "Remedial Option 2"
.
.
Now all I need to do now is work out how to insert the new sheet in the correct place in the work book i.e. after sheet named "Remedial Option 1" ?
I've noticed that the .Add has parameters (Before, After, Count, Type) but I don't know how to use these !
Dim myExcelWorkBook As New Excel.Workbook
Dim myExcelAddWorkSheet As Excel.Worksheet
.
.
.
Set myExcelAddWorkSheet = myExcelWorkBook.Worksheets.Add
myExcelAddWorkSheet.Name = "Remedial Option 2"
.
.
Now all I need to do now is work out how to insert the new sheet in the correct place in the work book i.e. after sheet named "Remedial Option 1" ?
I've noticed that the .Add has parameters (Before, After, Count, Type) but I don't know how to use these !
- Status
Similar threads
