EDITED 14 may
After a lot of reading I finally understand the basics of VBA. I have created the macro below, but it still isn't working, it won't insert the csv files. Afte this macro is finished the saved files are all empty. With debug.print I confirmed the string to the files is complete, but still something is missing?
Can anybody help me fix this problem
thanks in advance
Sub CSVimporterennaarxlsx()
'On Error Resume Next
'declare variable
Application.ScreenUpdating = False
Dim strpath As String
Dim fmn As Integer
Dim lmn As Integer
Dim csvname As String
Dim strpathcsvname As String
'active workbook pathway
strpath = Application.ActiveWorkbook.Path
'ask user for first and last number
fmn = InputBox("first mouse number")
lmn = InputBox("last mouse number")
'einde sub if inputbox is empty
' If fmn = "" Then
' MsgBox "No first mouse number"
' Exit Sub
' End If
' If lmn = "" Then
' MsgBox "No Last mouse number"
' Exit Sub
' End If
'assign variables
'loop all the files
For fmn = fmn To lmn
csvname = "m" & fmn
strpathcsvname = strpath & "\" & csvname & ".csv"
'input of csv file
' ActiveSheet.Cells.Delete
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" + strpathcsvname, _
Destination:=Range(A1))
'filename without extension
.Name = csvname
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 850
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 _
, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, _
1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileTrailingMinusNumbers = True
End With
Call CsvToXlsx(ByVal csvname, strpath)
Next fmn
Application.DisplayAlerts = True
End Sub
Sub CsvToXlsx(ByVal csvname, strpath)
ChDir (strpath & "/verwerkt")
Application.DisplayAlerts = False
csvname = csvname & ".xlsx"
ActiveWorkbook.SaveAs Filename:=csvname, FileFormat:=51
End Sub