Hej.
Jag har ett problem som jag inte fattar alls hur jag ska lösa.
Jag hade ett makro där jag importerade en hög csv-filer och där delade sig allt upp i kolumner som det skulle. Sen byggde jag om makrot till att göra en massa andra saker också och då helt plötsligt fungerar det inte längre att dela upp csv-filen till kolumner. Jag har gått igenom TextToColumns flera gånger och kopierat den koden från den fungerande filen men det hjälper inte.
Edit:
Jag har nu testat lite mer och när jag kör den andra koden som inte fungerar i en tom arbetsbok så verkar första filen dela upp sig i kolumner som den ska men inte de efterföljande. Det fungerar inte heller om jag kör makrot i en fil där jag redan har importerat csv-filer.
Kod som fungerar:
==============================
Sub Combinecsv1()
Dim xFilesToOpen As Variant
Dim I As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
Dim xScreen As Boolean
On Error GoTo ErrHandler
xScreen = Application.ScreenUpdating
Application.ScreenUpdating = False
xDelimiter = "|"
xFilesToOpen = Application.GetOpenFilename("All files (*.*), *.*", , "Kutools for Excel", , True)
If TypeName(xFilesToOpen) = "Boolean" Then
MsgBox "No files were selected", , "Kutools for Excel"
GoTo ExitHandler
End If
I = 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
xTempWb.Sheets(1).Copy
Set xWb = Application.ActiveWorkbook
xTempWb.Close False
xWb.Worksheets(I).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=True, _
Comma:=True, Space:=False, _
Other:=True, OtherChar:="|"
Do While I < UBound(xFilesToOpen)
I = I + 1
Set xTempWb = Workbooks.Open(xFilesToOpen(I))
With xWb
xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
.Worksheets(I).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=True, _
Comma:=True, Space:=False, _
Other:=True, OtherChar:=xDelimiter
End With
Loop
ExitHandler:
Application.ScreenUpdating = xScreen
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrHandler:
MsgBox Err.Description, , "Kutools for Excel"
Resume ExitHandler
End Sub
======================================
Sen har jag ändrat lite och helt plötsligt fungerar det inte längre.
======================================
Private Sub Combinecsv()
On Error GoTo ErrorHandler
Dim xFilesToOpen As Variant
Dim i As Integer
Dim j As Integer
Dim xWb As Workbook
Dim xTempWb As Workbook
Dim xDelimiter As String
MsgBox "Select CSV files to import"
xFilesToOpen = Application.GetOpenFilename("All files (*.*), *.*", , "Kutools for Excel", , True)
If TypeName(xFilesToOpen) = "Boolean" Then
MsgBox "No files were selected", , "Kutools for Excel"
GoTo ExitHandler
End If
' Use the active workbook
Set xWb = ThisWorkbook
xDelimiter = "|"
' Counter for sheet number
i = xWb.Sheets.Count
' Counter for files to open
j = 0
' Open up and paste all the selected csv files
Do While j < UBound(xFilesToOpen)
i = i + 1
j = j + 1
' Open up the csv file in Excel
Set xTempWb = Workbooks.Open(xFilesToOpen(j))
With xWb
' Paste the csv file to the last sheet
xTempWb.Sheets(1).Move after:=.Sheets(.Sheets.Count)
' Divide the text to columns
.Worksheets(i).Columns("A:A").TextToColumns _
Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=True, _
Comma:=True, Space:=False, _
Other:=True, OtherChar:=xDelimiter
End With
' Create a table and set the testnumber as the name
Application.Run "Make_table"
'Set the sheet to hidden
ActiveSheet.Visible = False
Loop
ExitHandler:
Set xWb = Nothing
Set xTempWb = Nothing
Exit Sub
ErrorHandler:
RaiseError Err.Number, Err.Source, "Mod_03_Create_file.Combinecsv", Err.Description, Erl
Resume ExitHandler
End Sub
===================================