SaveAs csv differs from vba csv export: vlookup values show up as #REF

[bchooser]

Hi,

I have a multisheet workbook that I am trying to export out to individual csv files. Within the worksheet I have a column with VLOOKUP values. When I SaveAs from the file menu, everything is perfect! However, when I export to csv from vba - I get the #REF! values where my VLOOKUP should be. help!

Sub CSV_Export()
Dim newWks As Worksheet
Dim wks As Worksheet


ActiveWorkbook.Save


' Get path to the current location of the IOMux excel sheet.
Dim withoutParts As String
withoutParts = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, "")


Application.DisplayAlerts = False


For Each wks In ActiveWorkbook.Worksheets
    If InStr("Instructions", wks.Name) = 0 Then
        wks.Copy 'to a new workbook
        Set newWks = ActiveSheet
        With newWks
        .SaveAs Filename:=withoutParts & wks.Name, FileFormat:=xlCSV
        .Parent.Close savechanges:=False
        End With
    End If


Next wks
Application.DisplayAlerts = True


MsgBox "done with: " & ActiveWorkbook.Name
End Sub

 

[Kenneth Hobson]

I guess you got the xlCSV from a recorded macro doing it the manual way?

Maybe try, xlCSVMSDOS.

Sub CreateCSVFromXLSsheets()
    Dim xlsheet As Excel.Worksheet
    Dim xlbook As Excel.Workbook
    Dim r As Excel.Range, r2 As Excel.Range
    Dim sht As Excel.Worksheet
    Dim nR As Long
    Dim csvPathName As String
     
    csvPathName = ActiveWorkbook.path & "\Test.csv"
     
    On Error GoTo TheEnd
     'Speed routines, http://vbaexpress.com/kb/getarticle.php?kb_id=1035
    SpeedOn
     
    Application.ScreenUpdating = False
    Set xlbook = Application.Workbooks.Add
    Set xlsheet = xlbook.Worksheets.Add
     
    xlsheet.Name = "CSV"
    For Each sht In ThisWorkbook.Worksheets
        nR = LastNBRow(xlsheet.UsedRange) + 1
        Set r = xlsheet.Range("A" & nR)
        Set r2 = ThisWorkbook.Worksheets(sht.Name).Range(RangeLR1(sht.UsedRange).Address)
        r.Resize(r2.Rows.Count, r2.Columns.Count).Value = r2.Value
    Next sht
     
    On Error Resume Next
     'Delete old csv file if it exists
    Kill csvPathName
    On Error GoTo TheEnd
    xlbook.SaveAs fileName:=csvPathName, FileFormat:=xlCSVMSDOS, CreateBackup:=False
    xlbook.Close False
     
TheEnd:
    On Error Resume Next
    Set xlsheet = Nothing
    Set xlbook = Nothing
    SpeedOff
     
     'Open csv file to see if it was created ok.
    Shell "cmd /c Notepad " & """" & csvPathName & """"
End Sub