Nec11
04-30-2013, 04:22 AM
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:
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
I use this code to save the text file on the server:
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
I use this code to load them in an third workbook :
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
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 :doh:
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:
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
I use this code to save the text file on the server:
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
I use this code to load them in an third workbook :
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
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 :doh: