Importing CSV issue/ find and activate

jjcarter

New Member
Joined
Aug 5, 2016
Messages
6
I am trying to design a macro to automate importing a csv file. I currently put the filename without the extension in column A.

I currently have the macro so it lets you choose the file first, then loops through column A to find that file name without extension. I want to offset by one column (to column B) and import the information starting there.

The macro runs, however, column A is is moved to column U and the import happens at A1? Just can't seem to figure out what I am doing wrong.

Any help would be most appreciated.

Code:
Sub CSVauto()
'
' CSVauto Macro
'
' Keyboard Shortcut: Option+Cmd+x
'
'   Declaring and setting variables for choosing CSV to import
    Dim csvFileName As Variant
    
    
'Prompt window to choose csv file
csvFileName = Application.GetOpenFilename(FileFilter:="")
If csvFileName = False Then Exit Sub


'Setting a variable to find Experimental form name in Data Summary
Dim whatToFind As String




'Declaring that variable
csvNoExt = Replace(csvFileName, ".csv", "")
whatToFind = csvNoExt


'Looping through A column to find csvFileName without .csv extension
    Columns("A:A").Select
        Set cell = Selection.Find(What:=whatToFind, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
        
'Selecting cell in column B for csv input
    If Not cell Is Nothing Then
        cell.Select
    End If
    
Dim newRange As Range
Set newRange = Range(ActiveCell, ActiveCell.Offset(0, 1))
    
    
'Formatting for CSV and input
With newRange.Parent.QueryTables.Add(Connection:="TEXT;" & csvFileName, Destination:=newRange)
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlMacintosh
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = True
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = True
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .Refresh BackgroundQuery:=False
        .UseListObject = False
    End With
 
 'Formatting DataSummary sheet to fit "requirements" :)
    Cells.Replace What:=">=", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByColumns, MatchCase:=False
    Cells.Replace What:="C121", Replacement:="C2", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False
    Cells.Replace What:="P1211", Replacement:="P21", LookAt:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=False
        


End Sub
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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
Back
Top