CODEWITHSUNDEEP
vbaexcelexport-to-csv
Gene
Gene

Reputation: 134

API PUT in VBA, exporting a CSV from range of cells (Microsoft Excel)

I am trying to export a bit of data from an excel workbook via API PUT. The specific API that I am using requires the data to be in CSV.

I have code from this site that converts a range of cells into a CSV.

Sub ExportRangetoFile()
'Update 20150628
Dim Rng As Range
Dim WorkRng As Range
Dim xFile As Variant
Dim xFileString As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, 
Type:=8)
Application.ActiveSheet.Copy
Application.ActiveSheet.Cells.Clear
WorkRng.Copy Application.ActiveSheet.Range("A1")
Set xFile = CreateObject("Scripting.FileSystemObject")
xFileString = Application.GetSaveAsFilename("", filefilter:="Comma Separated 
Text (*.CSV), *.CSV")
Application.ActiveWorkbook.SaveAs Filename:=xFileString, FileFormat:=xlCSV, 
CreateBackup:=False
End Sub

1st Issue: This example requires a couple extra steps after running; selecting the range and saving/naming the file. I have a constant range of cells that will be converted to CSV (although they do not have constant value) and I don't really want the user to do anything with the CSV. I am trying to make this as automated as possible so others can just click a button in the workbook to do this export. How can I do what this site shows but completely automated via macros and/or modules in VBA?

2nd Issue: If a solution to issue 1 is found, and a CSV file is saved after every time running that macro, is it possible to PUT that CSV via the API call in the same workbook the file was created? Again I am trying to make everything automated to make it as easy as possible for the people who will be using this workbook the most. Perhaps all of these things can be done in one macro or module.

Clarification

I am asking for help with creating a CSV from a range of cells by just running a macro and not clicking any saves or file naming. Secondly, I am just asking if it is possible and if it is best practice to send this CSV file through an API call in the same workbook or even the same macro where it can all be done with just one form click. I don't need help with the API call specifics... yet.

Upvotes: 0

Views: 376

Answers (2)

Techie
Techie

Reputation: 181

Hi @Gene below is modified code
note: modify somename as your requirment and date format and range in Set WorkRng = workbook.sheet1.range("A1:D " & lastrow & "") hope this will works for you.

Sub ExportRangetoFile()
'Update 20150628

Dim Rng As Range

Dim WorkRng As Range

Dim xFile As Variant

Dim xFileString As String

On Error Resume Next
xTitleId = "KutoolsforExcel"
 lastrow= workbook.sheet1.range("A "& rows.count & "").end(xlup).row 
 Set WorkRng = workbook.sheet1.range("A1:D " & lastrow & "")
'Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, 
Type:=8)
Application.ActiveSheet.Copy
Application.ActiveSheet.Cells.Clear
WorkRng.Copy Application.ActiveSheet.Range("A1")
Set xFile = CreateObject("Scripting.FileSystemObject")
xFileString = "somename"& format(now(),"ddmmyyyy")
'Application.GetSaveAsFilename("", filefilter:="Comma         Separated 
Text (*.CSV), *.CSV")
Application.ActiveWorkbook.SaveAs Filename:=xFileString, FileFormat:=xlCSV, 
CreateBackup:=False
End Sub

Upvotes: 0

Cyril
Cyril

Reputation: 6829

To build on my comment: 

Sub ExportRangetoFile()
'Update 20150628
    Dim Rng As Range, s_row as long, s_col as long, e_row as long e_col as long
    Dim xFile As Variant
    Dim xFileString As String
    'define range
    s_row = 1    'start row
    s_col = 1    'start column
    e_row = 200  'end row
    e_col = 5    'end column
    'back to normal code
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    Application.ActiveSheet.Copy
    Application.ActiveSheet.Cells.Clear
    'using defined range to copy, then paste
    Range(Cells(s_row,s_col),Cells(e_row,e_col)).Copy Application.ActiveSheet.Range("A1")
    Set xFile = CreateObject("Scripting.FileSystemObject")
    xFileString = Application.GetSaveAsFilename("", filefilter:="Comma     Separated Text (*.CSV), *.CSV")
    Application.ActiveWorkbook.SaveAs Filename:=xFileString, FileFormat:=xlCSV, 
    CreateBackup:=False
End Sub

Upvotes: 1

Related Questions