Adding VBA Code to Project

[RajJol]

Hi,

I really need some help on a work project using Excel VBA. My manager has wrote some code which does varies things to a document (i.e. opens, saves it in different format, copies and pastes other sheets onto one within excel etc) and I found a problem which I was asked to fix.

Basically, I need to add the letter "D" in front of every entry within column "Y" (stating from cell "Y2" down) of the spreadsheet. Then later remove this letter "D". My manager showed me how to do this but the code I wrote does not work and I know some code is missing for it to be fully working. Please see the code below.

Code needs to be written under "Adds the prefix D to date col as dates were converting wrong" and "Opens CSV to remove D from date field" headings.


Option Explicit

Sub FormatSector()

Dim wbSource As Workbook
Dim sht As Object
Dim XLFinalRow As Long

Application.ScreenUpdating = False

'saves and reopens as excel2007 workbook
Workbooks.Open FileName:="\\file03\shared\SHARE\work\workingFiles\Data.xls"

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:="\\file03\shared\SHARE\work\workingFiles\Data.xlsx" _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True

Windows("Data.xlsx").Close


Workbooks.Open FileName:="\\file03\shared\SHARE\work\workingFiles\Data.xlsx"


'Copy data to one sheet
Set wbSource = ActiveWorkbook

For Each sht In wbSource.Sheets
sht.Select

If ActiveSheet.Name <> "Sheet1" Then

Range(Cells(1, 1), Cells(xlLastRow, xlLastCol)).Copy
Sheets("Sheet1").Select
Cells(xlLastRow + 1, 1).Select
ActiveSheet.Paste

End If

Next

'delete all other sheets
For Each sht In wbSource.Sheets
If sht.Name <> "Sheet1" Then

Application.DisplayAlerts = False
sht.Delete
Application.DisplayAlerts = True
End If
Next

'Adds the prefix D to date col as dates were converting wrong

Range("Y2").Select
ActiveCell.Offset(1, 0).Select

'remove commas
Cells.Select

Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

'save as csv ready for inport
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:= _
"\\file03\shared\SHARE\work\workingFiles\Data.csv", FileFormat:= _
xlCSV, CreateBackup:=False

Windows("Data.csv").Close

'Opens CSV to remove D from date field
Workbooks.Open FileName:= _
"S:\SHARE\work\workingFiles\Data.csv"

Range("y2", "y") & XLFinalRow.Select

Selection.Replace What:="D", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Windows("Data.csv").Close
Application.DisplayAlerts = True

'Convert mainSpreadSheet to csv
Workbooks.Open FileName:="\\file03\shared\SHARE\work\workingFiles\DataToCheck.xlsx"

'remove commas
Cells.Select

Selection.Replace What:=",", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

Application.DisplayAlerts = False
ActiveWorkbook.SaveAs FileName:= _
"\\file03\shared\SHARE\work\workingFiles\DataToCheck.csv", FileFormat:=xlCSV, _
CreateBackup:=False

Windows("DataToCheck.csv").Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Ready for import!"

End Sub



Please someone help me!!

 

[SkipVought]

Hi,

"Adds the prefix D to date col as dates were converting wrong"

Is this excersize required because you are OPENING a .csv file with excel, and thereby getting incorrect results in a date column?

If so, a much MUCH better approch for opening ANY sort of TEXT file in Excel, is to IMPORT the data into an existing workbook. By using the Data > Import External Data > IMPORT feature, you can control the data conversions that are required (and prevent the conversions that Excel AUTOMATICALLY uses by default) in the import process.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}