PDA

View Full Version : [SOLVED] Code To Save Worksheets As Individual .txt But Only The First 9 Columns Of Each sheet



jameswoods89
01-18-2017, 02:09 AM
Hi All,

I'm new to VBA, just started reading some books but looking for a bit of help in the meantime. I'm currently running the below code which saves all the worksheets in my workbook as individual files (in this case .txt). I'm wondering if there is a way to only save the first 9 columns from the left i.e. A to I.


Sub RenameTabs()
'Updateby20140624
For x = 1 To Sheets.Count
If Worksheets(x).Range("B3").Value <> "" Then
Sheets(x).Name = Worksheets(x).Range("B3").Value
End If
Next
End Sub



Any help would be greatly appreciated.

Many thanks,

James

GTO
01-18-2017, 02:25 AM
Greetings and welcome to VBAX :hi:

Maybe you posted the wrong code?

Please use code tags when posting code. Like:


' Your code goes here


Mark

jameswoods89
01-18-2017, 02:33 AM
Hi Mark,

Thanks for that, apologies I posted the wrong code....forgive me it's quite late here.

The correct code is below




Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xcsvFile = CurDir & "\" & xWs.Name & ".txt"
Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, _
FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End Sub




I was originally exporting the files at .CSV hence why the name says CSV still

James

p45cal
01-18-2017, 03:30 AM
You could add one line:
Sub ExportSheetsToCSV()
Dim xWs As Worksheet
Dim xcsvFile As String
For Each xWs In Application.ActiveWorkbook.Worksheets
xWs.Copy
xcsvFile = CurDir & "\" & xWs.Name & ".txt"
ActiveSheet.Range(ActiveSheet.Range("J1"), ActiveSheet.Cells(1, ActiveSheet.Columns.Count)).EntireColumn.Clear '<< add this line.
Application.ActiveWorkbook.SaveAs Filename:=xcsvFile, FileFormat:=xlCSV, CreateBackup:=False
Application.ActiveWorkbook.Saved = True
Application.ActiveWorkbook.Close
Next
End Sub

I haven't changed the rest because I don't know what you want to use as a delimiter.

snb
01-18-2017, 04:25 AM
A csv-file consisting of the first 9 columns:


Sub M_snb()
Sheet1.Cells(1).CurrentRegion.Resize(, 9).Copy

With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
.GetFromClipboard
CreateObject("scripting.filesystemobject").createtextfile("G:\OF\saemple.csv").write .GetText
End With
End Sub

jameswoods89
01-18-2017, 04:52 AM
Thanks p45cal & snb I'll give both of these a try tomorrow and let you know. Appreciate you both taking the time!

James

jameswoods89
01-18-2017, 06:00 PM
Hi,

p45cal & snb just wanted to give you an update to let you know that both pieces of code work great!

Thank you so much for the help.

James