[RESOLVED] Create New Workbook

[maurice_whittaker]

Firstly, thank you to Si; I made progress with the Long Path problem but I can't see it on here to set it RESOLVED.

I am in deeper water now in trying to Create a new workbook. This coding has been working fine for months but is now giving error "Type of file not consistent with its extension, (.xls here). The coding I am using is as below & I only copied this without understanding it properly.

Create_New_Workbook MyBook ' Create a new W/Book and name it MyBook
Workbooks.Open Filename:=MyBook ' now open it.
ActiveWorkbook.Save
ActiveWindow.Close


Private Sub Create_New_Workbook(MyBook As String)
Dim xlsApp As Excel.Application
Set xlsApp = Excel.Application
With xlsApp
' .Show Excel
.Visible = True
' Create New Workbook
.Workbooks.Add
.ActiveWorkbook.Worksheets(1).Range("A3") = "Hello" ' This just avoids a possible error by saving an empty Book
.ActiveWorkbook.Close SaveChanges:=True, Filename:=MyBook, Routeworkbook:=False
End With
Exit Sub
End Sub

I had assumed that the xlsApp above determined an xls file and I am very puzzled as to why it should work well and then badly. Any help would be much appreciated.

[westconn1]

you should avoid using the activeworkbook, instead set the new book to a workbook object like

' Create New Workbook
set wb = .Workbooks.Add
wb.Worksheets(1).Range("A3") = "Hello" ' This just avoids a possible error by saving an empty Book
wb.Close SaveChanges:=True, Filename:=MyBook, Routeworkbook:=False

if the excel version installed is > 2003 then the default extention would be .xlsx (this would match your error)
if you want to save to a .xls, you should issue a wb.saveas where you can specify the filetype to save to as well as the file extention to suit, then wb.close false

[maurice_whittaker]

Thanks Pete, This is very useful. I am now inclined to go with the default of .xlsx rather than fight the system! I got very confused some time ago with the differences, which caused me to stay with .xls I am very grateful that there are chaps like you with the patience to help. Maurice

[incidentals]

have you actually updated you office package and found this error after that update?

would be good to know in light of the comments made

here to talk

[Hack]

Moved to Office Development

[maurice_whittaker]

Incidentals: No I have not updated; The major problem came when I tried to adopt westconn1's suggestion which was Set wb = .WorkBooks.Add and the compiler did not like the .WorkBooks bit
This is due to my ignorance as to whether the "Set" should go inside a "With loop" or before it and whether a "Dim" is required for wb and if so what should the "Dim" be?
I have tried quite a few variations, but not the right one!
Thanks.

[maurice_whittaker]

Hack: Sorry, it has been quite a while since I was on the site and I had forgotten.

[Siddharth Rout]

Thanks Pete, This is very useful. I am now inclined to go with the default of .xlsx rather than fight the system! I got very confused some time ago with the differences, which caused me to stay with .xls I am very grateful that there are chaps like you with the patience to help. Maurice

Even when you are saving it as default xlsx, you will get a problem.

To elaborate on what Pete mentioned, if you are using Excel version which is 2007 and greater then you need to set the file format as below

51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

So for example if you want to save the file as .xls then you have to use the following code.

Wb.SaveAs Filename:=MyBook, FileFormat:= 56

Similarly for xlsx, the above code will be

Wb.SaveAs Filename:=MyBook, FileFormat:= 51

HTH

Sid

[westconn1]

I am now inclined to go with the default of .xlsx rather than fight the system!

if your program could be used with different versions of excel you would have to provide code to handle differently, which ever way you go
as 2003 or < requires the xls extention and would not recognise the fileformat for xlexcel12 (or event the constant for xlexcel8 if xl2000)

[maurice_whittaker]

Thanks coolsid, I have taken note of that but I am still unable to compile the "wb = .WorkBooks.Add" line! When I succeed I will incorporate your suggestion.

[maurice_whittaker]

Thanks Pete, My version came with the 2007 version of Office and I will not be changing for some years. How do I ensure that my system recognizes "wb = .WorkBooks.Add" ??? Do I have to Dimension wb as Object, WorkBook or something? I tried Object and it didn't work!

[westconn1]

wb would be a workbook object so can be a workbook, object or variant
if you are doing this inside xl2007 you may not require the [B]set [\b]*keyword, so try without
i had assumed originally you were working in vb6, as your post was in the vb6 forum

[Siddharth Rout]

Maurice, since you are using 2007, here is a quick example. I could have written it in more compact manner but I have deliberately broken it into small parts for the sake of clarity.

Sub Sample()
    Dim FlName As String, Extn As String
    Dim FileExtn As Long
    
    FlName = "C:\MyFile"
    Extn = ".xls"
    
    Select Case UCase(Extn)
    Case ".XLSX": FileExtn = 51
    Case ".XLSM": FileExtn = 52
    Case ".XLSB": FileExtn = 50
    Case ".XLS": FileExtn = 56
    End Select
    
    Create_New_Workbook FlName & Extn, FileExtn
End Sub

Private Sub Create_New_Workbook(MyBook As String, Flfmt As Long)
    Dim wb As Workbook
    
    Set wb = Workbooks.Add
    wb.Sheets(1).Range("A3") = "Hello"
    wb.SaveAs Filename:=MyBook, FileFormat:=Flfmt
    wb.Close SaveChanges:=False
    
    Set wb = Nothing
End Sub

HTH

Sid

[westconn1]

Private Sub Create_New_Workbook(MyBook As String)
Dim xlsApp As Excel.Application
Set xlsApp = Excel.Application
With xlsApp
' .Show Excel
.Visible = True
' Create New Workbook
.Workbooks.Add

i based the code on the posted sample, where you were working with an application object for whatever reason, that is why i believed initially your code was in vb6
if you are now not using an application object remove the . from in front of .workbooks.add

[maurice_whittaker]

OOH ECK! Thanks all, This will take me a while to assimilate. My fiddling about got it to compile with Dim wb as FileStyles and removing the dot before WorkBooks. More luck than judgement, I'm afraid. Even then when I check what I have against all your suggestions it may have only compiled and not worked. I will not yet mark my string as RESOLVED. I don't want to be too hasty again! I am much obliged for all your help everyone.

[maurice_whittaker]

Hello again everybody! I feel like Frank Spencer here! I find that my version of VB is 6.5 which is a version of VB 6 isn't it? Anyway I think I am Ok now with the main problem of New WorkBook Creation so I will mark this RESOLVED. However, I have another thrilling episode to come for you to look forward to?? I will start another string for this on (hopefully correctly) VB6. Thanks again everybody you have been very kind.

[westconn1]

vb6 (visual basic 6) is for creating stand alone programs (exe files)
vb 6.3 or 6.5 etc (depending on the application hosting it) are all VBA (visual basic for applications)
and depending on the application hosting, as to what objects and methods are natively available to it, from any application you can automate other applications

much of the code that will run in vb6 can run in VBA and vice versa, but some of the objects would need to be created when required

vba queries should be posted in office development forum, rather than the VB6 forum, even though in many cases the answer would be the same, but specifically queries regarding application objects are more likely to get responses in office development

[maurice_whittaker]

Thanks again Pete, I am self-taught and I don't think the teacher was all that good! I saw your reply too late to avoid posting in the wrong place. I did remember the code tags though!

[westconn1]

doing it right helps to get best response
generally i reply to any in this forum first, then the vb6
probably a moderator will move in due course