Solved

Access 2007 csv to Excel

Posted on 2011-02-17
4
454 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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

777 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