Excel Compatibility issue

[Nec11]

Hell to all,

I am using different files of excel on two versions of excel 2007 and 2010.

The people work on it and by the end I am using an VBA to save on text file on the server some Sheets.

This create the history Sheet in excel:
[VBA]Sub istoricL12()
Application.Calculation = xlCalculationManual
mLN = (Sheet9.Cells(Sheet9.Rows.Count, 1).End(xlUp).Row)
If Sheet9.Range("B" & mLN) <> Sheet5.Range("D2") Then
mLN = mLN + 1
End If
Call deblocheaza
Sheet9.Range("A" & mLN) = Sheet5.Range("A2")
Sheet9.Range("B" & mLN) = Sheet5.Range("D2")
Sheet9.Range("C" & mLN) = Sheet5.Range("D14")
Sheet9.Range("D" & mLN) = Sheet5.Range("D15")
Sheet9.Range("E" & mLN) = Sheet5.Range("G5")
Sheet9.Range("F" & mLN) = Sheet5.Range("C3")
Sheet9.Range("G" & mLN) = Sheet5.Range("E3")
Sheet9.Range("H" & mLN) = Sheet5.Range("A3")
Sheet9.Range("I" & mLN) = Sheet5.Range("H3")
Sheet9.Range("J" & mLN) = Sheet5.Range("G3")
Sheet9.Range("L" & mLN) = Sheet5.Range("G4")
Sheet9.Range("M" & mLN) = Sheet5.Range("G5")
Sheet9.Range("N" & mLN) = Sheet5.Range("C10")
Sheet9.Range("O" & mLN) = Sheet5.Range("G6")
'Call Blocheaza
Application.Calculation = xlCalculationAutomatic
End Sub[/VBA]

I use this code to save the text file on the server:

[VBA]Sub export_istoricL12()
Application.Calculation = xlCalculationManual
Dim filename, db_location As String
Dim lastLN, lastCO As Integer
db_location = "S:\Somarest\prodtige\-=PROIECT SCAN=-\ALMA\database\"
filename = "i_" & Sheet7.Range("B2") & ".som"
lastLN = (Sheet9.Cells(Sheet9.Rows.Count, 1).End(xlUp).Row)
sep = "|"
On Error Resume Next
Kill (db_location & filename)
Open db_location & filename For Append As #2
For i = 2 To lastLN
Line = ""
For j = 1 To 14
Line = Line & Sheet9.Cells(i, j) & sep
Next j
Write #2, Line
Next i
Close #2
Application.Calculation = xlCalculationAutomatic
End Sub[/VBA]

I use this code to load them in an third workbook :

[VBA]Sub read_files()
Application.Calculation = xlManual
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim path
Sheet4.Range("A:W").ClearContents
i = 2
sep = "|"
Set objFSO = CreateObject("Scripting.FileSystemObject")
path = ThisWorkbook.path & "\database\"
Set objFolder = objFSO.GetFolder(path)
For Each objFile In objFolder.Files
If Left(objFile.Name, 2) = "i_" Then
Open path & objFile.Name For Input As #1
Do While Not EOF(1)
Input #1, Line
Dim x
x = Split(Line, sep)
For j = 0 To UBound(x)
Sheet4.Cells(i, j + 1) = x(j)
Next j
i = i + 1
Loop
Close #1
End If
Next
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
mLN = (Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row)
If Sheet1.Range("A3" & mLN) <> Sheet4.Range("A1") Then
mLN = mLN + 1
End If
Application.Calculation = xlAutomatic
End Sub[/VBA]

The problem by the end is with the number formatting because the Excel 2007 save the number formats as 102.35 and Excel 2010 save the number formats as 102,35, and by the end after loading those informations in the third sheet the numbers from EX 2007 will look as normal ones (102.35 but thoseones from EX 2010 are reeded like text (10235489752147,00).

Any solution on this problem?
I have tryed different number formats in EX 2010 but unsucceded

[snb]

wouldn't this suffice ?


[vba]Sub istoricL12()
Application.Calculation = xlCalculationManual
deblocheaza

with sheet5
Sheet9.Cells(Rows.Count, 2).End(xlUp).offset(1,-1).resize(,15)=array(.[A2],.[D2],.[D14],.[D15],.[G5],.[C3],.[E3],.[A3],.[H3],.[G3],"",.[G4],.[G5],.[C10],.[G6])
end with

Blocheaza
Application.Calculation = xlCalculationAutomatic
End Sub[/vba]


[vba]Sub export_istoricL12()
Application.Calculation = xlCalculationManual

sn=sheet9.columns(1).specialcells(2).offset(1).specialcells(2).resize(,14)
for j=1 to ubound(sn)
c00=c00 & vbcrlf & join(application.index(sn,j,0),";")
next
createobject("scriptingfilesystemobject").createtextfile("S:\Somarest\prodt ige\-=PROIECT SCAN=-\ALMA\database\i_" & Sheet7.Range("B2") & ".csv").write mid(c00,3)

Application.Calculation = xlCalculationAutomatic
End Sub[/vba]

[Nec11]

Your code looks nice, But:
1. I need to keep one record by the day.
2.And most importanty are the figures by the end of the day.
3.This code did not solve my problem.

The problem is the modality of writing numbers by the Excel in to Text File.
As I sed the EX2007 writhe them like 102.35 -> CORRECT
but
EX 2010 Write them like 1023584766,00-> INCORRCET

I need to force EX2010 to writh them as 102.35

Actualy this point "." is the problem

See the attachet picture (this was the fastest way to attach something, becauste the excel file is too bing).


P.S. I'm affraid with the second VBA is something wrong
Tryed to correct it but... stil fail.

[snb]

It's only a dot in the second macro:


[vba]Sub export_istoricL12() [/vba]
[vba]

Application.Calculation = xlCalculationManual

sn=sheet9.columns91).specialcells(2).offset(1).specialcells(2).resize(,14)
For j=1 To UBound(sn)
c00=c00 & vbcrlf & join(application.index(sn,j,0),";")
Next
createobject("scripting.filesystemobject").createtextfile("S:\Somarest\prod tige\-=PROIECT SCAN=-\ALMA\database\i_" & Sheet7.Range("B2") & ".csv").write mid(c00,3)

Application.Calculation = xlCalculationAutomatic

End Sub

[/vba]

if you use this macro you can open the file using

[VBA]
Workbooks.open "S:\Somarest\prodtige\-=PROIECT SCAN=-\ALMA\database\i_" & Sheet7.Range("B2") & ".csv"
[/VBA]

But to be honest: what's the point in writing data to a file if you can import them directly into another workbook ?

[Nec11]

Because that txt file I wont to be able to load it also în other excel files.
I have tryed before with linking workbooks between them but unsuccesful.

I will try again tour code în few hours.
Regards.

[snb]

This is all you need to export/import from workbook export.xls to workbook import.xls

[vba]
Sub M_snb()
with workbooks("export.xls").sheet5.columns(1).specialcells(2).offset(1).special cells(2).resize(,15)
workbooks("Import.xls").Sheet9.Cells(Rows.Count, 2).End(xlUp).offset(1,-1).resize(.rows.count,15)=.value
End With
end sub
[/vba]

[Nec11]

guys, is not working.
the damn file looks the same, with coma instead a dot...

is getting me crazy.

tried also with:
[VBA]Sheet9.Range("J:J").NumberFormat = "0.00%"
Sheet9.Range("K:K").NumberFormat = "0.00"[/VBA]

failure...

[snb]

Which damn file are you talking about ?
Why not posting 2 samples ?

[Nec11]

the text files created by the code to export data to server.
I would be pleased to load them here but this site does not allow me to load txt files.
The picture from the post #3 can guide you.

[snb]

you can zip any txt file.

[Nec11]

Attached ZIP File.

[snb]

The hyphens in each file cause the confusion:
Adapt the path G:\OF\

[vba]
Sub M_snb()
With CreateObject("scripting.filesystemobject")
.createtextfile("G:\OF\new.csv").write Replace(Replace(.opentextfile("G:\OF\" & Dir("G:\OF\*.som")).readall, "|", ","), Chr(34), "")
.createtextfile("G:\OF\new_001.csv").write Replace(Replace(.opentextfile("G:\OF\" & Dir).readall, "|", ","), Chr(34), "")
End With

Workbooks.Open "G:\OF\new.csv"
Workbooks.Open "G:\OF\new_001.csv"

End Sub[/vba]