Some adjustment to functional vba code

SimbadS

New Member
Joined
Feb 13, 2020
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.:

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!
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,941
Office Version
  1. 365
Platform
  1. Windows
Open a CSV file is different than opening an Excel file, as it is a Text Delimited File, and needs an import conversion routine.
Importing another Excel file is easier, as you don't need to go through that.
See this thread for some ways of doing it: VBA Macro to Ask User to Browse for Excel File to Open
 

Watch MrExcel Video

Forum statistics

Threads
1,122,795
Messages
5,598,083
Members
414,210
Latest member
Janisogor

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top