OnePlus 5 review
hi i have a spreadsheet with all items and correct prices i wish to import to sage instant accounts however the format needs to be csv,i can save as csv but still there is criteria that cannot be imported ie spaces " , / is there a way of editing columns to remove the above so import will be seamless
YEAH DIDNT WORK
I think you actually have to type a space in the top field (find what) but leave the replace field blank.
This is not at all clear, moorie. What exactly are you trying to do.
And shouting 'YEAH DIDNT WORK' will help nobody, let alone you.
hi sorry wasnt shouting accidently knocked caps lock on and posted reply without realising
what im trying to do is import a pricelist into sage instant accounts.it has to be in csv format to accept it,also it will not accept spaces in code descriptions or symbols such as ",%, etc.otherwise import will be unsuccessful.when saving the excel spreadsheet in csv format it retains the symbols.what i was wondering is there a way of removing the symbols from the spreadsheet rather than manually correcting each error. there are over 4000 symbol references in the spreadsheet.
hi hope this is clear?
By default a .csv (Comma Separated Value) file will contain commas (,).
You will get quotation marks in text files even when you save manually if entry in a single cell has list separator (generally comma). You can avoid quotation marks while saving manually, by changing the list separator in your computer (Control Panel | Regional Settings | Number | List Separator) to some character other than the character you have in your cell content.
Visual Basic (unlike Excel itself) doesn't take often the regional settings in account. It will always save CSV files with the delimiter used in the US (comma). Although disk I/O with text files is very slow in Visual Basic, you should use an appropriate macro. An example (taken from elsewhere) is below.
Function Exporte(Wksht As Worksheet, NomFic As String, _
Optional Remplace As Boolean = True) As Long
Dim UsedRange As Range
Dim NbCols As Integer, NbLignes As Long
Dim Stat As Integer, Incr As Integer
Dim I As Integer, J As Integer
Dim Progr As Integer
On Error GoTo Erreur
If Dir(NomFic) < "" And Not Remplace Then
Exporte = -1
Open NomFic For Output As #1
Set UsedRange = Wksht.UsedRange
NbCols = UsedRange.Columns.Count - 1
NbLignes = UsedRange.Rows.Count
Stat = NbLignes / 40
Incr = Stat
For I = 1 To NbLignes
If I = Stat Then
Stat = Stat + Incr
Progr = Progr + 1
Application.StatusBar = _
"Exportation " & String(Progr, ".")
For J = 1 To NbCols
Print #1, Cstr(UsedRange(I, J)) & ";";
Print #1, Cstr(UsedRange(I, J))
Exporte = Err
Application.StatusBar = False
Dim Result As Long
Result = Exporte(ActiveSheet, "Test.csv", False)
Select Case Result
MsgBox "File does Not exist", vbExclamation
MsgBox "File exported."
This VBA procedure will save the active worksheet as tab delimited (text):
ActiveWorkbook.SaveAs FileName:="C:\My Documents\MyFile.txt", FileFormat:=xlText
If none of this helps, post your question at MrExcel.com
This thread is now locked and can not be replied to.