Link to home
Start Free TrialLog in
Avatar of Wilder1626
Wilder1626Flag for Canada

asked on

VB6 - Save as XML Spreadsheet 2003 format

Hi,

I have below code that copy what's in MSHFlexgrid3 into an Excel file. Up to now, i don't have any problem.

What i want to do is to save the file in XML Speadsheet 2003 (formatFile = 46 if i'm not mistaken.)

Using below code do save it as .xml but everytime i extract, i need to re-save as XML Speadsheet 2003 and then it works.

Would you understand why? How can I fix it?

Thank you for your help.

 Dim xlObj As Object    'New Excel.Application -- only used with Excel reference
    Dim wkbOut As Object   'Excel.Workbook
    Dim wksOut As Object   'Excel.Worksheet
    Dim rngOut As Object   'Excel.Range
    Dim sngStart As Single    'forperformance measurement
    Dim start_time As Long
    Dim end_time As Long
    Dim total_time As Long
    Dim FileNm As Variant


    'Save file to
    With CommonDialog1
        .DialogTitle = "Data Extract..."
        .Filename = environment_id.Text & " - extract"
        .CancelError = True  '<--moved
        .Filter = "Spreadsheet Files (*.xml)|*.xml| 2k7 Excel Files (*.xml)|*.xml"
        .ShowSave


        If Err.Number = 32755 Then
            MsgBox "not saved - user pressed cancel or closed the dialog"
            Exit Sub
        Else
            FileNm = .Filename
            path_link = FileNm
        End If

    End With
	
    'output to Excel workbook
    '  lblStatus.Caption = "Begin Excel Data Export"
    Set xlObj = CreateObject("Excel.Application")
    Set wkbOut = xlObj.Workbooks.Add
    Set wksOut = wkbOut.Worksheets("Sheet1")  'can skip this step
    Set rngOut = wksOut.Range("A1")           'by replacing with wkbOut.Worksheets("Sheet1").Range("A1")

    Me.MousePointer = vbHourglass
    Me.Enabled = False

    xlObj.ScreenUpdating = False
    xlObj.Calculation = -4135     '=xlCalculationManual

	
    '***************************************************************     
	
	  MSHFlexgrid3 copy to excel
	
	'***************************************************************



xlObj.ActiveWorkbook.SaveAs FileNm & ".xml"

xlObj.Calculation = -4105     '=xlCalculationAutomatic
xlObj.ScreenUpdating = True
xlObj.Visible = True

Set rngOut = Nothing
Set wksOut = Nothing
Set wkbOut = Nothing
Set xlObj = Nothing

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Wilder1626

ASKER

Thank you. this is working great.
Pleased to help