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

[ggooss]

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?

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]

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]

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]

Another approach would be to do the data preparation in Access then write to a text file with csv extension using VBA.

 

[ggooss]

Thank you very much for the help.

'arnelgp
.Application.ActiveWorkbook.SaveAs filename:=strFullName, _
fileformat:=6, _
CreateBackup:=True

worked

 

[arnelgp]

you're welcome!

 

[ggooss]

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]

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]

Format the data in a query and export the query using TransferText.

 

[ggooss]

Thank you for your prompt reply, unfortunately, i still get the following message...

 

[Pat Hartman]

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]

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]

The only formatting you are doing is with the date.

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

Export the query using TransferText