[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Saving a VB6-generated Excel with Format option

Posted on 2012-09-10
6
Medium Priority
?
387 Views
Last Modified: 2012-09-11
My VB6 App generates an Excel spreadsheet.
There is an issue with the format in which it is saved.
There are a lot of Excel formats, depending on the users MS Office version.
I would like to execute a "generic" save format, if that is possible, so the user isn't bothered with a system-generated warning.

Here is a sample of stripped-down code:

Sub GenerateExcelSpreadsheet()
'
Dim oXLApp As Object
Dim oXLBook As Object
Dim oXLSheet As Object
Dim iOption As Integer
Dim sWorkbookName As String
Dim sOutputPath As String
'
iOption = -4143 ' Or 56 ???
sWorkbookName = "TEST"
sOutputPath = "C:\Trash\" & sWorkbookName
'
Set oXLApp = CreateObject("Excel.Application")
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
'
With oXLApp
' ----
' ----Populate the cells
' ----
End With
'
oXLBook.SaveAs ---- Format:=iOption
'
Set oXLApp = Nothing
Set oXLBook = Nothing
Set oXLSheet = Nothing
'
End Sub

Open in new window


My question pertains to line 24:
What is the preferred way to save the spreadsheet, specifying a generic format?
0
Comment
Question by:NormaPosy
  • 3
  • 2
6 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38387149
I believe Excel started using xlsm with Excel 2007 (which is version 12) so you can use code like this.

If Application.Version > "11.0" Then
    iOption = 56
Else
    iOption = -4143
End If

Open in new window


Here's a page to look at if you need to know the version numbers of other versions of Excel.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38387703
There is a registry setting that can disable the non-native format alert message.
0
 
LVL 46

Expert Comment

by:aikimark
ID: 38387707
You can also create a tab-delimited file and give the file an XLS extension
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 38387817
Sincew you're doing this from VB6 I should probably modify my code to add the Excel object.

If oXLApp.Application.Version > "11.0" Then
    iOption = 56
Else
    iOption = -4143
End If

Open in new window

0
 

Author Closing Comment

by:NormaPosy
ID: 38389207
Thank You
- - Norma
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 38389237
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question