I am new to macros and ahve cobbled together this macro. I am trying to
open .xy files (comma delimited text files), run some actions (remove rows,
add headers, delete columns) then save to a new directory as a .csv.
This macro seems to run correctly until the save. It won't save. any
thoughts on how to save this are greatly appreciated.
Charleen
Sub Read_Text_Files()
Dim sPath As String
Dim oPath, oFile, oFSO As Object
Dim r, iRow As Long
Dim wbImportFile As Workbook
Dim wsDestination As Worksheet
Dim CSVFolder As String
'Files location
sPath = "Y:\Projects_GIS\A_M\Sand_Mining\2014-Quarter 1\"
CSVFolder = "Y:\Projects_GIS\A_M\Sand_Mining\2014\"
Set wsDestination = ThisWorkbook.Sheets("Sheet1")
r = 8
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oPath = oFSO.GetFolder(sPath)
Application.ScreenUpdating = False
For Each oFile In oPath.Files
If LCase(Right(oFile.Name, 3)) = ".xy" Then
'open file to import
Workbooks.OpenText Filename:=oFile.Path, Origin:=437,
StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True,
Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1,
1), _
Array(2, 1)), TrailingMinusNumbers:=True
Rows("1:8").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
ActiveCell.FormulaR1C1 = "Epoch"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Year"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Month"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Day"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Hour"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Minute"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Second"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Voltage"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Longitude"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Latitude"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Knots"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Degrees"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Status"
Columns("I:J").Select
Selection.NumberFormat = "0.00000"
Columns("N:AG").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("K12").Select
wb.SaveAs Filename:=CSVFolder & Left(oFile.Name, Len(oFile.Name) -
3) & ".csv", FileFormat:=xlCSV_
wb.Close False
End If
Next oFile
End Sub
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
