asad9100
03-24-2022, 08:54 AM
Hi there, I am facing an issue with VBA. I am creating a csv file from one of the sheet and the from csv I am creating a text file for BCP to upload it to SQL server. The issue is that till the creation of CSV the data looks good but when I save it as text file then few columns have double quotes " " with them. I need the data without double quotes. The piece of code where it says "save the csv file". I tried to change the format to xlTextPrinter which solves the proble of double quotes but then the BCP is not able to read the file for upload.
Any help would be great. Thanks in advance..
Her is my VBA code
Public Sub ExportSheetToSQL(Tabname As String, Filename As String, Tablename As String, firstRow As String)
'define variables
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
'get name of the workbook
CurrentWorkbook = ThisWorkbook.FullName
CurrentWorkbookName = ThisWorkbook.Name
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = GetTempDirectory & ""
'make sure that the temp directory exists
'If Len(Dir(GetTempDirectory(), vbDirectory)) = 0 Then
'MkDir GetTempDirectory()
'End If
'copy the workbook
'it is necessary to save it as an CVS file
Set CVSWorkbook = Workbooks.Add
With CVSWorkbook
.Title = "CVS"
.Subject = "CVS"
.SaveAs Filename:=SaveToDirectory & "XLS" & Filename & ".xls"
End With
Workbooks(CurrentWorkbookName).Activate
Worksheets(Tabname).Select
Worksheets(Tabname).Copy Before:=CVSWorkbook.Sheets(1)
'clear formats
'it is necessary to get rid of the USD format
'CVSWorkbook.Worksheets(Tabname).Range("A:XZ").ClearFormats
Dim lastRowIndex As Long
lastRowIndex = 0
lastRowIndex = Worksheets(Tabname).Range("A200000").End(xlUp).Row
'save the csv file
CVSWorkbook.SaveAs Filename:=SaveToDirectory & Filename, FileFormat:=xlTextWindows
Dim TargetWorkbook As String
TargetWorkbook = CVSWorkbook.FullName
' MsgBox TargetWorkbook
End Sub
Any help would be great. Thanks in advance..
Her is my VBA code
Public Sub ExportSheetToSQL(Tabname As String, Filename As String, Tablename As String, firstRow As String)
'define variables
Dim WS As Excel.Worksheet
Dim SaveToDirectory As String
Dim CurrentWorkbook As String
Dim CurrentFormat As Long
'get name of the workbook
CurrentWorkbook = ThisWorkbook.FullName
CurrentWorkbookName = ThisWorkbook.Name
CurrentFormat = ThisWorkbook.FileFormat
' Store current details for the workbook
SaveToDirectory = GetTempDirectory & ""
'make sure that the temp directory exists
'If Len(Dir(GetTempDirectory(), vbDirectory)) = 0 Then
'MkDir GetTempDirectory()
'End If
'copy the workbook
'it is necessary to save it as an CVS file
Set CVSWorkbook = Workbooks.Add
With CVSWorkbook
.Title = "CVS"
.Subject = "CVS"
.SaveAs Filename:=SaveToDirectory & "XLS" & Filename & ".xls"
End With
Workbooks(CurrentWorkbookName).Activate
Worksheets(Tabname).Select
Worksheets(Tabname).Copy Before:=CVSWorkbook.Sheets(1)
'clear formats
'it is necessary to get rid of the USD format
'CVSWorkbook.Worksheets(Tabname).Range("A:XZ").ClearFormats
Dim lastRowIndex As Long
lastRowIndex = 0
lastRowIndex = Worksheets(Tabname).Range("A200000").End(xlUp).Row
'save the csv file
CVSWorkbook.SaveAs Filename:=SaveToDirectory & Filename, FileFormat:=xlTextWindows
Dim TargetWorkbook As String
TargetWorkbook = CVSWorkbook.FullName
' MsgBox TargetWorkbook
End Sub