AW: Zelleninhalte Export / Import
06.08.2012 09:41:48
fcs
Hallo Jürgen,
da du Inhalte aus mehreren Blättern exportieren/importieren möchtest muss du in der Textdatei auch die Information speichern, aus welchelchem tabellenblatt die Inhalte stammen.
Ich hab Sepps Makros mal in diese Richtung angepasst.
Gruß
Franz
' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' Original-Code von Josef Ehrensberger
Option Explicit
Sub exportValuesToText()
Dim vntFile As Variant
Dim wks As Worksheet, strRange As String
Dim rng As Range, rngC As Range, lngZeile As Long, lngSpalte As Long
Dim ff As Integer
On Error GoTo Fehler
vntFile = Application.GetSaveAsFilename("Werte.txt", "Text Files (*.txt), *.txt")
If vntFile False Then
ff = FreeFile
Open vntFile For Output As #ff
Set wks = Worksheets("Einstellungen")
strRange = "C2:C13,B16,B19,B20,B21"
Set rngC = wks.Range(strRange)
For Each rng In rngC.Cells
Print #ff, wks.Name & ";" & rng.Address(0, 0) & ";" & rng.Formula
Next
Set wks = Worksheets("Übersicht")
For lngSpalte = 3 To 25 Step 2 'SPalten C, E, bis Y
For lngZeile = 6 To 36
Set rng = wks.Cells(lngZeile, lngSpalte)
Print #ff, wks.Name & ";" & rng.Address(0, 0) & ";" & rng.Formula
Next
Next lngSpalte
Close #ff
End If
Fehler:
With Err
Select Case .Number
Case 0 'alles OK
Case Else
MsgBox "Fehler-Nr. " & .Number & vbLf & .Description
Close
End Select
End With
Set rng = Nothing
Set rngC = Nothing
Set wks = Nothing
End Sub
Sub importValuesFromText()
Dim strFile As String, strTmp As String
Dim ff As Integer
On Error GoTo Fehler
strFile = Application.GetOpenFilename("Text Dateien (*.txt),*.txt")
If strFile CStr(False) Then
ff = FreeFile
Open strFile For Input As #ff
Do While Not EOF(ff)
Line Input #ff, strTmp
With Worksheets(Split(strTmp, ";")(0)).Range(Split(strTmp, ";")(1))
.Formula = Split(strTmp, ";")(2)
End With
Loop
Close #ff
End If
Fehler:
With Err
Select Case .Number
Case 0 'alles OK
Case Else
MsgBox "Fehler-Nr. " & .Number & vbLf & .Description
Close
End Select
End With
End Sub