CODEWITHSUNDEEP
vbaexcel
Dyhouse
Dyhouse

Reputation: 83

Save as .CSV and .txt

I am sure this is straightforward but is baffling me beyond belief. I have written the below code. You can see in to places that I am saving My pay final and Non-mypay Final as CSV files. Is there a way I can get the files to save as txt files as well. Ideally with the same location if possible? As always, your help is greatly appreciated.

 Option Explicit

 Sub BACSConversion()

 Dim MyNewBook As String
 Dim MySaveFile As String
 Dim fileToOpen As Variant
 Dim fileName As String
 Dim sheetName As String
 Dim rCopy As Range

 'Turn off display alerts
 Application.DisplayAlerts = False
 'Turn off screen updates
 Application.ScreenUpdating = False

 'Ensures that the file open directory is always the same
  ChDir "S:\MERIT OUTPUTS FOLDER\MSI Recruitment Limited\"

 'Opens the folder to location to select txt file
  fileToOpen = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If fileToOpen <> False Then
    Workbooks.OpenText fileName:=fileToOpen, _
    DataType:=xlDelimited, Tab:=True
    End If
 'Creates the file name based on txt file name
  fileName = Mid(fileToOpen, InStrRev(fileToOpen, "\") + 1)
 'Creates the sheet name based on the active txt file
  sheetName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 4)

 'Save active file as...
  ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\BACS File Original\" & _
  fileName & ".CSV")

 'Selects all data in column A and copies to clipboard
  Set rCopy = Range("A1", Range("A1").End(xlDown))

 'Open the original document where the BACS file is located
   Workbooks.Open "S:\Accounts (New)\Management Information 
 (Analysis)\Phil Hanmore - Analysis\bacs conversation calc.xlsx"
 'Selects the worksheet called "Original"
  Sheets("Original").Range("A:A").ClearContents

 'Paste selected values from previous sheet
  rCopy.Copy
  Sheets("Original").Range("A1").PasteSpecial Paste:=xlPasteValues

 'Selects appropriate worksheet - Non-MyPayFINAL
   Sheets("Non-MyPay FINAL").Select

  'Selects all data in column A and copies to clipboard
   Range("A1", Range("A1").End(xlDown)).Select
  Selection.Copy

  'Add a new workbook
   Workbooks.Add
 'Paste selected values from previous sheet
  Selection.PasteSpecial Paste:=xlPasteValues

 'Build SaveAs file name
  MySaveFile = Format(Now(), "DDMMYYYY") & "NonMyPayFINAL" & ".CSV"
 'Save template file as...
  ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\" & MySaveFile)
 'Close the new saved file
   ActiveWorkbook.Close

 'Selects appropriate worksheet - MyPayFINAL
   Sheets("MyPay FINAL").Select

 'Selects all data in column A and copies to clipboard
 Range("A1", Range("A1").End(xlDown)).Select
 Selection.Copy

 'Add a new workbook
  Workbooks.Add
 'Paste selected values from previous sheet
 Selection.PasteSpecial Paste:=xlPasteValues

 'Build SaveAs file name
  MySaveFile = Format(Now(), "DDMMYYYY") & "MyPayFINAL" & ".CSV"
 'Save template file as...
  ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment 
  Limited\" & MySaveFile)
  'Close the new saved file
   ActiveWorkbook.Close
 'Close original source workbook (template)
  Workbooks("bacs conversation calc").Close

 'Turn on display alerts
Application.DisplayAlerts = True
 'Turn on screen updates
Application.ScreenUpdating = True

 End Sub

Upvotes: 2

Views: 2867

Answers (1)

Busse
Busse

Reputation: 863

After you save it as a .csv file, just re-do the same code but instead of .csv change it to .txt

But that won't necessarily change the type of file. After your Activeworkbook.SaveAs command, add FileFormat:= xlTextWindows. This will actually save it as a textfile.

It should look like:

ActiveWorkbook.SaveAs ("S:\MERIT OUTPUTS FOLDER\MSI Recruitment Limited\" & MySaveFile), FileFormat:= xlTextWindows

I would recommend also looking up the multiple different types of "FileFormats" when it comes to saving workbooks, just to familiarize yourself with them. Just search Excel FileFormats, and you should be able to find information about all of them.

Update:

As mentioned in the comments, your CSV file you are saving may not be actually saving as comma delimited. To do so, similarly to what I said above, you can change the fileformat of your initial save to xlCSV. This can be achieved by adding FileFormat:= xlcsv to the end of your first ActiveWorkbook.SaveAs

FileFormats are required when you are actually interested in changing the filetype and not just the extension. Here is the link to the MSDN site explaining all the different formats. MSDN XLFileFormats. Instead of using the text xlCSV or xlTextWindows you can also use the numeric values assigned to those formats. Instead of FileFormat:= xlCSV you can do FileFormat:= 6 or for xlTextWindows you can do FileFormat:= 20

Upvotes: 3

Related Questions