The code that I propose is to create, starting from data contained in the sheets in a workbook, a series of csv file (or txt).
The Save As file of this type in fact carried out by VB code does not recognize decimal delimiters incorrectly, so the comma is replaced by point ...
is used a reference library to use the Scripting FileSystemObject.
to get the huge potential of the FSO will find many examples in the help of VBScript. Leditor VBScript accessible from the menu Tools-> Macro> Microsoft Script Editor ... (You may need to install the first access to any OK) ... Editor online help is then available chapter vbscript within which you will find topics related to the FileSystemObject. Greetings
r
Option Explicit 'in a standard form of the VBA project
'______________________________________________ 'Di Roberto Mensa nick r
'______________________________________________
'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'crea per ogni foglio della cartella di lavoro
'un file csv o txt (modificando la costante
'Estensione_file)
'i file vengono salvati nel percorso nella stessa
'posizione della cartella attiva 'se un file csv con lo stesso percorso-nome esiste 'già sarà sovrascritto
Dim Sh As Excel.Worksheet
Dim Rng As Excel.Range
Dim S As String
Dim FSO As Object
Dim tS As Object
Dim Wb As Excel.Workbook
Dim sPath As String
Const ForWriting As Long = 2
Const Estensione_file As String = ".csv"
Set Wb = ActiveWorkbook
Set FSO = CreateObject("Scripting.FileSystemObject")
sPath = Wb.Path '
On Error Resume Next
For Each Sh In Wb.Worksheets
Set Rng = UsedRange_Value(Sh, , True)
S = CSV_text(Rng)
Set tS = FSO.OpenTextFile(FSO.BuildPath( _
sPath, Sh.Name & Estensione_file _
), ForWriting, True)
tS.Write S
tS.Close
Next
End Sub
Function CSV_text( _
Rng As Excel.Range, _ Option D
As String = "") As String '
'______________________________________________ << eventualmente da cambiare
¯¯¯¯¯¯¯¯¯¯¯¯¯
'by Robert Mensa nick r
'______________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'parameter opzionale D indica il delimitatore
'da utilizzare nella scrittura del file
Dim R As Long, C As Long
Dim S As String, St As String
For R = 1 To Rng.Rows.Count
St = ""
For C = 1 To Rng.Columns.Count
St = St & D & Rng(R, C).Text
Next C
If Len(Replace(St, D, "")) Then
St = Right(St, Len(St) - 1)
Else
St = ""
End If
S = S & St & VBA.Constants.vbNewLine
Next R
CSV_text = VBA.Left(S, Len(S) - 2)
End Function
Function UsedRange_Value( _
Optional Sh As Worksheet, _
Optional Rng As Range, _
Optional WithFormulas As Boolean = False) _ As Excel.Range
'______________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'by Robert Mensa nick r
'______________________________________________
' ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
'Returns the minimum range rectangular
' which includes all cells enhanced
'in sheet or in the range that we spend as
' argument
'Returns Nothing if no cell is
' enhanced
'will be ignored by passing Sh Rng
'not passing arguments will be sought
' active sheet in the range of
folder 'containing only the cells that contain active
' a constant value
'passing will
WithFormulas = True' also considered the cells with a
'formula
Dim S As String, ts As String Dim
RE As Object Dim
maxrate As Long Dim maxc
minR As Long As Long, Mincio As Long Dim V
'Check for the first two arguments and septum
' If the search range
Sh
Is Nothing Then If Rng Is Nothing Then
September Rng = [a1]. Parent.UsedRange
End If
September Sh = Rng.Parent
= Rng Else
September Sh.UsedRange
End If On Error Resume Next
'sets the range of cells containing a value
' constant
September UsedRange_Value Rng.SpecialCells = (_
xlCellTypeConstants)
' Control If the optional parameter
WithFormulas
Then 'add the cells that contain formulas
If TypeName (UsedRange_Value) = "Nothing" Then
September UsedRange_Value = _
Rng.SpecialCells (xlCellTypeFormulas, 23)
Else
September UsedRange_Value = _
Union (
UsedRange_Value _, _
Rng.SpecialCells (xlCellTypeFormulas, 23))
End If End If On Error GoTo 0
'verification that the range is not empty If
TypeName (UsedRange_Value) = "Range" Then
'I check if it contains more areas
If UsedRange_Value.Areas.Count> 1 Then
' recovery
set the coordinates to 'rectangular range
' Warning!
'behavior is not documented
' Address reported to range
'Rng.Address returns up to 257
' characters
For Each V In UsedRange_Value
S = S & V. _
Address (True, True, xlR1C1)
Next
Set RE = CreateObject("vbscript.regexp")
RE.Global = True
RE.Pattern = "C\d+:,"
tS = RE.Replace(S, "")
RE.Pattern = "\d+"
maxR = RE.Execute(tS)(0)
minR = maxR
For Each V In RE.Execute(tS)
If V
minR = V
ElseIf V > maxR Then
maxR = V
End If
Next
RE.Pattern = "R\d+:,"
tS = RE.Replace(S, "")
RE.Pattern = "\d+"
maxC = RE.Execute(tS)(0)
minC = maxC
For Each V In RE.Execute(tS)
If V
minC = V < minR Then
ElseIf V > maxC Then
maxC = V
End If Next
September UsedRange_Value Sh.Range = (_
Sh.Cells (minR, Mincio), _
Sh.Cells (maxrate, maxc))
End If End If End Function
< minC Then
0 comments:
Post a Comment