[RESOLVED] Need help exporting data to excel 2007
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
Re: Need help exporting data to excel 2007
There is an extra bracket in the sql statement because i edited please ignore cause its not in the original code.
Re: Need help exporting data to excel 2007
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 ?
Re: Need help exporting data to excel 2007
Why not dump it to Excel 2003 and just open it with 2007?
Re: Need help exporting data to excel 2007
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.
Re: Need help exporting data to excel 2007
Welcome to VBForums :wave:
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.
Re: Need help exporting data to excel 2007
Thanks for welcoming me to the forums. I'll check out the links.
Re: Need help exporting data to excel 2007
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.
vb 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
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
Re: Need help exporting data to excel 2007
You've got a significant typo there, I think it should be like this:
Code:
sql = "SELECT " & NewData & " INTO [Excel 12.0;DATABASE=" & strExcelFile & "].[" & wkshCallDetail & "] " & _
Re: Need help exporting data to excel 2007
oh yea that's how it looks my bad. :eek:
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
Re: Need help exporting data to excel 2007
It seems as if excel 2007 won't let you the above code, so i got it work with the copyfromrecordset.
Re: Need help exporting data to excel 2007
Quote:
Originally Posted by
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.
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
Re: Need help exporting data to excel 2007
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 ^.
Re: Need help exporting data to excel 2007
i got this to work. Please close !
Re: Need help exporting data to excel 2007
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. :)
Re: [RESOLVED] Need help exporting data to excel 2007