VB6 - Save as XML Spreadsheet 2003 format


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"

        If Err.Number = 32755 Then
            MsgBox "not saved - user pressed cancel or closed the dialog"
            Exit Sub
            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

LVL 11
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Try replacing line 56 with

xlObj.ActiveWorkbook.SaveAs FileNm & ".xml", FileFormat:=46

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wilder1626Author Commented:
Thank you. this is working great.
Roy CoxGroup Finance ManagerCommented:
Pleased to help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.