• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 485
  • Last Modified:

Access 2007 csv to Excel

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
HKFuey
Asked:
HKFuey
  • 2
1 Solution
 
borkiCommented:
What will the user run, once they receive the .csv file? Default would be Excel, is that your preferred solution?
0
 
HKFueyAuthor Commented:
Hi Borki, Ideally they need to use Excell (to do stock calculations etc.)
0
 
Rey Obrero (Capricorn1)Commented:
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
 
HKFueyAuthor Commented:
Hi Capricorn1, that's great, works perfect. Thanks!

I used  -4143 and also:

if xlfile>"" then kill xlfile

Thanks again.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now