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

ggooss

New member
Local time
Yesterday, 20:01
Joined
Nov 19, 2020
Messages
2
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
Yesterday, 20:01
Joined
Oct 29, 2018
Messages
13,707
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

error reading drive A:
Local time
Today, 12:01
Joined
May 7, 2009
Messages
11,121
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, 12:01
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
Yesterday, 20:01
Joined
Nov 19, 2020
Messages
2
Thank you very much for the help.
'arnelgp
.Application.ActiveWorkbook.SaveAs filename:=strFullName, _
fileformat:=6, _
CreateBackup:=True
worked :)
 

Users who are viewing this thread

Top Bottom