[RESOLVED] Need help exporting data to excel 2007

[supeazn]

Basically I'm taking data from an Access 2000 database and dumping it in Excel. Although this concept works for Excel 2003, but not Excel 2007. I'm using Vb6 for this process.

Sample Code:

'The dialog box that asks the user where to save the document or object
With CommonDialog1
.InitDir = customer_root
.DefaultExt = ".xlsx"
.Filter = "Excel Workbook (*.xlsx)|*.xlsx"
.ShowSave
.CancelError = True 'If the user clicks cancel then the program exits
the sub
End With

ExcelName = "Excel 8.0;DATABASE=" 'I've tried setting this to 12.0 but no luck.

sql = "SELECT " & NewData & " INTO [" Excel 8.0;DATABASE=C:\Test.xlsx].[12_2_2009]] " & _
" FROM NewTable " & _
" WHERE (Line < 900) OR (Line >= 900 AND Action01 = 1) ORDER BY EndStep"

Debug.Print sql

objDB.Execute sql

[supeazn]

There is an extra bracket in the sql statement because i edited please ignore cause its not in the original code.

[supeazn]

Running the above code i get this error

Error Code: 3027
Cannot update. Database or object is read-only.

it's because of the variable "ExcelName". Setting it to 12.0 gave another error about not being able to find the installable ISAM. Already googled it and went through all the scenarios provided by other experts like install the latest version of Jet and MDAC. Do you guys think vb6 understands the 4 character extension or Excel 2007 ?

[Pasvorto]

Why not dump it to Excel 2003 and just open it with 2007?

[supeazn]

Because I need to dump over 70,000 rows of data for a report. 2003 can only handle up to 65,000 and 2007 and handle over 1,000,000 (with some registry mod). It's for work.

[si_the_geek]

Welcome to VBForums

You haven't shown how you set up the connection, but I suspect you are using JET, which does not know about the recent file types. What you should be using instead is ACE, which you can get via the link in my signature (another link shows examples of connection strings).

Alternatively, if Excel will be installed on the computer your code is running on, you can use Automation to work with whatever version of Excel is installed. See my tutorial (link in my signature) for details.

[supeazn]

Thanks for welcoming me to the forums. I'll check out the links.

[supeazn]

Thanks si for the help. I was able to write the data to excel 2007, but now the other dilemma is it gives me an error when i try to open the file. Err msg: Excel cannot open " & insert file name & " the file format or file extension is not valid.

'The dialog box that asks the user where to save the document or object
        With CommonDialog1
            .InitDir = customer_root
            .DefaultExt = ".xlsx"
            .Filter = "Excel Workbook (*.xlsx)|*.xlsx"
            .ShowSave
            .CancelError = True 'If the user clicks cancel then the program exits the sub
        End With
        
            sql = "SELECT " & NewData & " INTO [" & Excel 12.0;DATABASE & strExcelFile & "].[" & wkshCallDetail & "] " & _
            " FROM NewTable " & _
            " WHERE (Line < 900) OR (Line >= 900 AND Action01 = 1) ORDER BY EndStep"
            cnnaccess.execute sql

[si_the_geek]

You've got a significant typo there, I think it should be like this:

            sql = "SELECT " & NewData & " INTO [Excel 12.0;DATABASE=" & strExcelFile & "].[" & wkshCallDetail & "] " & _

[supeazn]

oh yea that's how it looks my bad.

This is how it looks in debug mode



SELECT Onion,CallCounter AS [ORCA Call ID],NetworkConnect as [Network Connect],Start,EvaluatedResult,EndStep,Answer,Done,ANI,TestCase AS [Test Case],Call_Time,Data01,Time01,Data04_01,Time04_01,Data05_01,Time05_01,Audio INTO [Excel 12.0;DATABASE=C:\!Drill\_H&RBlock_CallDetailReport_20091202_TOOLaaa.xlsx].[12_2_2009] FROM NewTable WHERE (Line < 900) OR (Line >= 900 AND Action01 = 1) ORDER BY EndStep

[supeazn]

It seems as if excel 2007 won't let you the above code, so i got it work with the copyfromrecordset.

[Siddharth Rout]

Because I need to dump over 70,000 rows of data for a report. 2003 can only handle up to 65,000 and 2007 and handle over 1,000,000 (with some registry mod). It's for work.

I will suggest the same thing to you what I suggested this guy and I will leave the decision to you...

http://www.vbforums.com/showpost.php...3&postcount=13

[supeazn]

Thanks for the suggestion. Because we are in the mist of converting to Office 2007 and dumping 2003 (i know it sucks). I got it to work like i said ^.

[supeazn]

i got this to work. Please close !

[Hack]

If you consider your question to be resolved then pull down the Thread Tools menu and click the Mark Thread Resolved menu item. That will let everyone know that you have your answer.

Thank you.

[supeazn]

Kool thanks !