Solved Export a Query, Format and save as CSV

ggooss

ggooss

New member
Local time
Today, 02:26
Joined
Nov 19, 2020
Messages
10
Hello,

I am trying to adjust the code below (that works) so I end up with a .csv instead of a .xls

Each attempt to replace the .xls with .csv resulted
Run-time error '3027'
Cannot update, Database or Object is read only.

Could someone help me figure out what i am missing please?

Code: 
Private Sub Command12_Click()


Dim stamp As String
stamp = Format(Now, "yyyymmddhhnnss")


DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "qryPO_ImportInJS", "TEST.xls", True
Call ModifyExportedExcelFileFormats("TEST.xls", "qryPO_ImportInJS")
End Sub



Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)
On Error GoTo Proc_Error

    Dim xlApp As Object
    Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
With xlApp
    .Application.Rows("1:1").Select
    .Application.Selection.Delete Shift:=xlUp
    .Application.Rows("1:5").Select
    .Application.Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Application.Range("A1").Select
    .Application.ActiveCell.FormulaR1C1 = "Amazon"
    .Application.Range("I6").Select
    .Application.Selection.Copy
    .Application.Range("A3").Select
    .Application.ActiveSheet.Paste
    .Application.Range("G6").Select
    .Application.Application.CutCopyMode = False
    .Application.Selection.Copy
    .Application.Range("A4").Select
    .Application.ActiveSheet.Paste
    .Application.Range("H6").Select
    .Application.Application.CutCopyMode = False
    .Application.Selection.Copy
    .Application.Range("A5").Select
    .Application.ActiveSheet.Paste
    .Application.Columns("G:I").Select
    .Application.Application.CutCopyMode = False
    .Application.Selection.Delete Shift:=xlToLeft
    .Application.Range("A3").Select
    .Application.Selection.NumberFormat = "yyyy/mm/dd"
    .Application.Range("A1").Select
    .Application.ActiveWorkbook.Save
    .Application.ActiveWorkbook.Close
    .Quit
End With

MsgBox "File Saved in :....."


Proc_Error:
Set xlApp = Nothing
Set xlSheet = Nothing


End Sub
 
Hi. Welcome to AWF!

A CSV file is just a Text file, so you can't have Formatting in it. Have you tried simply using the OutpuTo method?
 
Code: 
Public Sub ModifyExportedExcelFileFormats(sFile As String, sSheet As String)
On Error GoTo Proc_Error

    Dim xlApp As Object
    Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
With xlApp
    .Application.Rows("1:1").Select
    .Application.Selection.Delete Shift:=xlUp
    .Application.Rows("1:5").Select
    .Application.Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Application.Range("A1").Select
    .Application.ActiveCell.FormulaR1C1 = "Amazon"
    .Application.Range("I6").Select
    .Application.Selection.Copy
    .Application.Range("A3").Select
    .Application.ActiveSheet.Paste
    .Application.Range("G6").Select
    .Application.Application.CutCopyMode = False
    .Application.Selection.Copy
    .Application.Range("A4").Select
    .Application.ActiveSheet.Paste
    .Application.Range("H6").Select
    .Application.Application.CutCopyMode = False
    .Application.Selection.Copy
    .Application.Range("A5").Select
    .Application.ActiveSheet.Paste
    .Application.Columns("G:I").Select
    .Application.Application.CutCopyMode = False
    .Application.Selection.Delete Shift:=xlToLeft
    .Application.Range("A3").Select
    .Application.Selection.NumberFormat = "yyyy/mm/dd"
    .Application.Range("A1").Select
    .Application.ActiveWorkbook.Save
    
    'arnelgp
    .Application.ActiveWorkbook.SaveAs filename:=strFullName, _
                      fileformat:=6, _
                      CreateBackup:=True
    
    .Application.ActiveWorkbook.Close
    .Quit
End With

MsgBox "File Saved in :....."


Proc_Error:
Set xlApp = Nothing
Set xlSheet = Nothing


End Sub
 
Another approach would be to do the data preparation in Access then write to a text file with csv extension using VBA.
 
Thank you very much for the help.
'arnelgp
.Application.ActiveWorkbook.SaveAs filename:=strFullName, _
fileformat:=6, _
CreateBackup:=True
Click to expand...
worked :)
 
you're welcome!
 
Hello, actually, once this is saved as a CSV, how do i make sure it gets closed please?
'arnelgp
.Application.ActiveWorkbook.SaveAs filename:=strFullName, _
fileformat:=6, _
CreateBackup:=True
Click to expand...

With the current code, i end up with two files, the .xls and the .csv, but the latter stays open in the background.
 
Code: 
Public Sub ModifyExportedExcelFileFormats(ByVal sFile As String, Optional ByVal sSheet As String = "")
On Error GoTo Proc_Error

    Dim xlApp As Object
    Dim xlSheet As Object

Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    
With xlSheet
    .Rows("1:1").Select
    .Application.Selection.Delete Shift:=xlUp
    .Rows("1:5").Select
    .Application.Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    .Range("A1").Select
    .Application.Selection.FormulaR1C1 = "Amazon"
    .Range("I6").Select
    .Application.Selection.Copy
    .Range("A3").Select
    .Paste
    .Range("G6").Select
    .Application.Application.CutCopyMode = False
    .Application.Selection.Copy
    .Range("A4").Select
    .Paste
    .Range("H6").Select
    .Application.CutCopyMode = False
    .Application.Selection.Copy
    .Range("A5").Select
    .Paste
    .Columns("G:I").Select
    .Application.Application.CutCopyMode = False
    .Application.Selection.Delete Shift:=xlToLeft
    .Range("A3").Select
    .Application.Selection.NumberFormat = "yyyy/mm/dd"
    .Range("A1").Select
    .Application.ActiveWorkbook.Save
    
    'arnelgp
    '.Application.ActiveWorkbook.SaveAs FileName:=strFullName, _
    '                  FileFormat:=6, _
    '                  CreateBackup:=True
    .SaveAs strFullName, 6
    .Application.ActiveWorkbook.Close
End With

xlApp.Quit

MsgBox "File Saved in :....."

Proc_Error:
Set xlApp = Nothing
Set xlSheet = Nothing


End Sub
 
Format the data in a query and export the query using TransferText.
 
Thank you for your prompt reply, unfortunately, i still get the following message...
1612203978126.png
 
You didn't use TransferText. Using TransferText will NOT open Excel. A .csv is a plain text file and you can open it and update it using notepad.
 
Hello Pat, sorry, i missed your previous comment at 10:20 am.
Unfortunately, i do not have the option to edit the test in a query as the file has two parts to it, a header and the data set. (or i wouldn't know how to achieve that).

It's too bad, because at the moment, if i could get all the instances of Excel to close after the "saving as" .csv step, everything else works.
 
The only formatting you are doing is with the date.

Select Format(SomeDate, "yyyy/mm/dd") as FormattedDate1, ... From ...

Export the query using TransferText
 
You must log in or register to reply here.

Similar threads

MattBaldry
Solved Export and Format Excel
Replies
7
Views
959
Gasman
Gasman
MushroomKing
Exporting date fields to excel, but not received as a date field.
Replies
9
Views
5,382
Frothingslosh
Frothingslosh
P
how to export or save as in vba code
Replies
0
Views
1,041
pujangga2007
P
S
Closing Excel from Access VBA
Replies
2
Views
2,496
sparkyrose
S
C
Exporting to Excel template and saving as a new file
Replies
13
Views
1,457
GinaWhipp
GinaWhipp

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom