create a csv from an excel file in vb

[BADANDY]

i am working with creating a csv from an open work book and attaching it to an email i have the file generation down but when it does a saveas it generates an excel workbook and just adds a .csv on the end it does not create the csv

here is an example of my code

Sub EmailWithOutlook()
'Variable declaration
Dim oApp As Object
Dim oMail As Object
Dim WB As Workbook
Dim FileName As String
Dim WB2 As Workbook
Dim FileName2 As String
Dim strDate As String
Dim x As Integer
Dim y As Integer
Dim strBudCode As String



'Turn off screen updating
Application.ScreenUpdating = False


FileName = "" & ActiveWorkbook.Name & ""
FileName2 = "Import For" & FileName
'ActiveWorkbook.Copy
'ActiveSheet.Copy

'create import file


Set WB = ActiveWorkbook


Set WB2 = Workbooks.Add
With WB2



'WB2.Worksheets.Add ("Import")
'.ActiveSheet
'WB2.Worksheets.Visible
End With

Set WB2 = ActiveWorkbook
strDate = WB.Worksheets("Budget").Cells(3, 2)
strBudCode = WB.Worksheets("Budget").Cells(3, 5)

x = 7
y = 1

Do While WB.Worksheets("Budget").Cells(x, 1) <> ""


If WB.Worksheets("Budget").Cells(x, 14) <> 0 Then
'this is company
WB2.Worksheets("Sheet1").Cells(y, 1) = 1
'this is glacct
WB2.Worksheets("Sheet1").Cells(y, 2) = WB.Worksheets("Budget").Cells(x, 1)
'this is month ran for
WB2.Worksheets("Sheet1").Cells(y, 3) = "1 / 1 / " & Year(strDate) & ""
'this is budget
WB2.Worksheets("Sheet1").Cells(y, 4) = WB.Worksheets("Budget").Cells(x, 14) / 12
'this is budget code
WB2.Worksheets("Sheet1").Cells(y, 5) = strBudCode
y = y + 1
x = x + 1
Else

x = x + 1
End If
'y = y + 1
Loop


On Error Resume Next

Kill Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\MY documents\My Music\" & "" & FileName2 & ".csv"
'Kill Environ("HOMEDRIVE") & Environ("HOMEPATH") & FileName
On Error GoTo 0
'WB.SaveAs FileName:=Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\MY documents\My Music\" & FileName
WB2.SaveAs Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\MY documents\My Music\" & "" & FileName2 & ".csv", FileFormat:=xlCSV


'Create and show the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
'send to person or group
.To = "bfrancisco@occimofab.com; davidw@occimofab.com"
'subject of email
.Subject = "test submital of " & WB.Name & " "
.attachments.Add WB.FullName
.attachments.Add WB2.FullName
.Display


'Delete the temporary file
'Kill Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\MY documents\My Music\" & "" & FileName2 & ".csv"

WB2.ChangeFileAccess Mode:=xlReadOnly


WB2.Close SaveChanges:=False
MsgBox "Processed" & FileName



'Kill WB2.FullName
'Restore screen updating and release Outlook
Application.ScreenUpdating = True

End With

Set oMail = Nothing
Set oApp = Nothing

End Sub

any help would be appreciated

thanks
andy

 

[stilesbn]

You might want to try adding the "Filename:=" to the line where you save WB2. So....

WB2.SaveAs Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\MY documents\My Music\" & "" & FileName2 & ".csv", FileFormat:=xlCSV

becomes...

WB2.SaveAs Filename:=Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\MY documents\My Music\" & "" & FileName2 & ".csv", FileFormat:=xlCSV

 

[BADANDY]

sorry i found my error it wasnt in the generation it was in the numbers. my numbers were padded with spaces