Hi!,
I have (found) this working vba code below for import CVS files as i want.
Thing is that iam not sure what i have to change to make it import correct an xlsx file instead.:
What changes i have to make to make a clean import from a xlsx file? (excel file).
Thank you!
I have (found) this working vba code below for import CVS files as i want.
Thing is that iam not sure what i have to change to make it import correct an xlsx file instead.:
VBA Code:
Sub Importfile()
Dim FileSelect As Variant
Dim WBIntern As Workbook, WBEkstern As Workbook
Dim i As Long
Dim LastColumn As String
Dim customer As Worksheet
Dim InputKB As Worksheet, Eksternfil As Worksheet
Dim Addme As Range, Copydata As Range
Dim qryTable As QueryTable
Dim c As Variant
Set WBIntern = ActiveWorkbook
Set InputKB = WBIntern.Worksheets("Import")
Application.ScreenUpdating = False
Sheets("Import").Select
Columns("A:Y").Select
Selection.ClearContents
Set Addme = InputKB.Range("A1")
'On Error GoTo ErrorhandlerImportInput
FileSelect = Application.GetOpenFilename(filefilter:="CSV Files,*.csv", _
MultiSelect:=False)
If FileSelect = False Then
MsgBox "Chose file please"
Exit Sub
End If
'Workbooks.OpenText Filename:=FileSelect, local:=True 'Startrow:=1, DataType:=xlDelimited, _
TextQualifier:=xlSingleQuote, ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=True, _
Comma:=True, Space:=True, Other:=False, local:=True
Set qryTable = InputKB.QueryTables.Add(Connection:="TEXT;" & FileSelect, Destination:=InputKB.Range("A1"))
With qryTable
.Name = "csvQuery"
.FieldNames = True
.AdjustColumnWidth = True
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
'.TextFileColumnDataTypes = myarray
.refresh BackgroundQuery:=False
End With
qryTable.Delete
Application.ScreenUpdating = True
Exit Sub
ErrorhandlerImportInput:
WBEkstern.Close False
Application.ScreenUpdating = True
MsgBox ("Import failed, try again")
End Sub
What changes i have to make to make a clean import from a xlsx file? (excel file).
Thank you!