maleodillet
New Member
- Joined
- Feb 15, 2018
- Messages
- 12
Hi Gurus,
I have this VBA Code that I using to copy an Excel range and then use it to create a CSV file. It is using "PasteValuesOnly" to get the Job done but when a cell from the range is empty it pastes a Zero when I actually want it to keep the empty cell on the newly created CSV file. Any suggestions? please see my code below:
Sub CreateDatafile()
Dim content As String
Dim rng As Range
Set rng = Range("Q20:Y40")
Dim Path As String
Dim FileName1 As String
Dim sWB As Workbook, _
sWS As Worksheet
Dim dWB As Workbook, _
dWS As Worksheet
Path = "C:\Users\maleo\Desktop\MASTERDATA_LOADING"
'Path = "\ROOT\WEBFOLDERS\PLNSBX\SUMMARY\DATAMANAGER\DATAFILES\ or C:\Users\maleo\Desktop\MASTERDATA_LOADING"
FileName1 = Range("R16")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet
Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)
sWS.Range("Q20:Y40").Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWB.SaveAs Filename:=Path & FileName1 & ".csv", FileFormat:=xlCSV
dWB.Close False
MsgBox "The Updated Master Data file has been created"
End Sub
I have this VBA Code that I using to copy an Excel range and then use it to create a CSV file. It is using "PasteValuesOnly" to get the Job done but when a cell from the range is empty it pastes a Zero when I actually want it to keep the empty cell on the newly created CSV file. Any suggestions? please see my code below:
Sub CreateDatafile()
Dim content As String
Dim rng As Range
Set rng = Range("Q20:Y40")
Dim Path As String
Dim FileName1 As String
Dim sWB As Workbook, _
sWS As Worksheet
Dim dWB As Workbook, _
dWS As Worksheet
Path = "C:\Users\maleo\Desktop\MASTERDATA_LOADING"
'Path = "\ROOT\WEBFOLDERS\PLNSBX\SUMMARY\DATAMANAGER\DATAFILES\ or C:\Users\maleo\Desktop\MASTERDATA_LOADING"
FileName1 = Range("R16")
Set sWB = ActiveWorkbook
Set sWS = sWB.ActiveSheet
Set dWB = Workbooks.Add
Set dWS = dWB.Sheets(1)
sWS.Range("Q20:Y40").Copy
dWS.Range("A1").PasteSpecial xlPasteValues
dWB.SaveAs Filename:=Path & FileName1 & ".csv", FileFormat:=xlCSV
dWB.Close False
MsgBox "The Updated Master Data file has been created"
End Sub