[RESOLVED] Create an Excel Document
Hello,
I want to be able to write some information from my VBA to an Excel file, therefore I'm currently using
Code:
'Open browse window om bestand te selecteren
NewFN = Application.GetSaveAsFilename("C:\TestFile", "Excel file (*.xls), *.xls")
sFile = NewFN
If NewFN = False Then
Else
'open file
Workbooks.Open (sFile)
'select sheet1
ActiveWorkbook.Sheets("Sheet1").Select
Range("A1") = "Date"
Range("B1") = "N50 total"
This works fine when the Excel file already excists but when it doesn't it will give an error. Next to this I found some code to create a Excel file
Code:
Set xlsApp = Excel.Application
With xlsApp
'Show Excel
.Visible = True
'Create a new workbook
.Workbooks.Add
End With
I have two questions about this: 1 How do I check if the Excel file already excist, so if it doesn't I could create a new document. And 2 How can I save the newly created file with the name selected in NewFN = Application.GetSaveAsFilename("C:\TestFile", "Excel file (*.xls), *.xls")?
Thanks for your help,
Barry
Re: Create an Excel Document
Already found it myself, for the one who wants to know this is how
Code:
Public Function FileFolderExists(strFullPath As String) As Boolean
If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
End Function
Private Sub CommandButton1_Click()
NewFN = Application.GetSaveAsFilename("C:\Documents and Settings\hyperthermie\Mijn documenten\QA Excel\N50\N50 waarden 1H", "Excel file (*.xls), *.xls")
sFile = NewFN
str = NewFN
If NewFN = False Then
Else
If FileFolderExists(str) = True Then
'open File
Workbooks.Open (sFile)
etc.
Else
Set xlsApp = Excel.Application
With xlsApp
'Show Excel
.Visible = True
'Create a new workbook
.Workbooks.Add
'Write
'Save the workbook
ActiveWorkbook.SaveAs (str)
'and exit
ActiveWorkbook.Close
End With
End If
End If
