Solved Export a Query, Format and save as CSV (1 Viewer)

ggooss

New member
Local time
Today, 04:59
Joined
Nov 19, 2020
Messages
8
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 04:59
Joined
Oct 29, 2018
Messages
15,542
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?
 

arnelgp

once i caught a fish alive...
Local time
Today, 19:59
Joined
May 7, 2009
Messages
13,202
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
 

jocph

Member
Local time
Today, 19:59
Joined
Sep 12, 2014
Messages
57
Another approach would be to do the data preparation in Access then write to a text file with csv extension using VBA.
 

ggooss

New member
Local time
Today, 04:59
Joined
Nov 19, 2020
Messages
8
Thank you very much for the help.
'arnelgp
.Application.ActiveWorkbook.SaveAs filename:=strFullName, _
fileformat:=6, _
CreateBackup:=True
worked :)
 

ggooss

New member
Local time
Today, 04:59
Joined
Nov 19, 2020
Messages
8
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

With the current code, i end up with two files, the .xls and the .csv, but the latter stays open in the background.
 

arnelgp

once i caught a fish alive...
Local time
Today, 19:59
Joined
May 7, 2009
Messages
13,202
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Feb 19, 2002
Messages
32,221
Format the data in a query and export the query using TransferText.
 

ggooss

New member
Local time
Today, 04:59
Joined
Nov 19, 2020
Messages
8
Thank you for your prompt reply, unfortunately, i still get the following message...
1612203978126.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Feb 19, 2002
Messages
32,221
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.
 

ggooss

New member
Local time
Today, 04:59
Joined
Nov 19, 2020
Messages
8
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:59
Joined
Feb 19, 2002
Messages
32,221
The only formatting you are doing is with the date.

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

Export the query using TransferText
 

Users who are viewing this thread

Top Bottom