Solved

Access 2007 csv to Excel

Posted on 2011-02-17
4
426 Views
Last Modified: 2012-06-27
I have an access app that outputs all the rows of a database table. I had to output to csv as excel has a row limitation. The app then sends the file as an email to various people, they then have to save as XL to add formatting etc.
Does anyone know how to convert the csv into xls/xlsx using VBA?
0
Comment
Question by:HKFuey
  • 2
4 Comments
 
LVL 9

Expert Comment

by:borki
ID: 34914560
What will the user run, once they receive the .csv file? Default would be Excel, is that your preferred solution?
0
 

Author Comment

by:HKFuey
ID: 34914911
Hi Borki, Ideally they need to use Excell (to do stock calculations etc.)
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 34915510
try this codes


Sub saveasXLS()
Dim xlObj As Object, xlFile As String, csvFile
csvFile = "C:\xxx.csv"
xlFile = "C:\xxx.xls"
Set xlObj = CreateObject("excel.Application")
    xlObj.Workbooks.Open (csvFile)
    xlObj.activeworkbook.saveas xlFile, FileFormat:=43, CreateBackup:=False
    xlObj.activeworkbook.Saved = True
    xlObj.Quit
End Sub

for xlsx use FileFormat:=51

if you have problem with FileFormat:=43, use,   -4143  or  56



0
 

Author Closing Comment

by:HKFuey
ID: 34917867
Hi Capricorn1, that's great, works perfect. Thanks!

I used  -4143 and also:

if xlfile>"" then kill xlfile

Thanks again.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now