Discussion Locked

Writing to Excel from Visual Basic 5.0

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I would like to write to an existing Excel file from Visual Basic 5.0. However, if this file does not exist, I would like to create one with that name. I do not want to actually open and view the Excel file though. How do I do this? Please include references needed and error checking. Thanks.

3 replies

by sridp

Hi, Check this out and do not Forget to rate
the Answer

Private Sub Command1_Click()
' You will need the ScrRun.Dll installed in your system,Else Try the Dir Fn. to check for the File.

Dim Mwb As Excel.Workbook, Mxl As Excel.Application, AddWb As Excel.Workbook
Dim AddWs As Excel.Worksheet, Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")

If Fso.FileExists("C:\TestExl1.Xls") Then 'Check if the file Exists
Set Mwb = GetObject("C:\TestExl1.Xls") 'If the File you are looking for is
'already in the system then get an instance of the file in the Variable Mwb
Else
Set Mxl = New Excel.Application ' IF the file is not Avlb. in your system then
'
Set AddWb = Mxl.Workbooks.Add 'Add a workBook to the newly created Xl - App. The
'Application's reference is stored in the variable Mxl
Set AddWs = AddWb.Worksheets.Add ' Add WorkSheet to the newly added workBook

AddWs.Cells(2, 2) = "Some text - here" 'Write some thing in the Xl
AddWs.SaveAs ("C:\NewExcelApp.Xls") 'Save the File in the as you want.
'release all the object refernces -> Imp. otherwise it may clogg your systems
'memory.
Set AddWb = Nothing
Set AddWs = Nothing

Exit Sub
End If

Dim Mws As Excel.Worksheet
Set Mws = Mwb.Worksheets(1) 'The if Cond. Will transfer the Control here if the
'File is found, Now you create a refernce to your sheet by setting it to an variable Mws

Mws.Cells(10, 3) = "Some Text -- Here" 'Modify\Change\Writesomething in your file
Mws.Visible = xlSheetVisible ' Does not make the XL app Visible. Property Setting to the Sheet.

Mws.SaveAs ("C:\TestExl1.Xls") 'Finally Save the Sheet.

Set Mxl = Nothing
Set Mwb = Nothing
Set Mws = Nothing
End Sub

This discussion is closed to new replies.