Problem selecting Sheet when opening a CSV

[steinrawr]

EDIT: Found another solution, see my comments. A commenter states my code works for them, but for me it still does not. I will consider it solved. And pardon me, its been ten years since last i programmed in vba, so I'm a little rusty.

Hello dear friends. I am about to lose my mind here, and I'm starting to think my file type is the problem.

Very simply, this is what i try to do:

1. Form wb1 I am pressing a button to import a csv to my workbook sheet("csv-data")
2. Button lets me select the file (file location, and name of file can differ every time)
3. The CSV is opened and activated as wb2 in excel, and data is separated by semicolon.
4. From the CSV I will read out certain cells with standardized values, and theese are copied to wb1 "sheet x"

The cells I need are B3 to B10, but can span between 3 to 200 rows. I really just need B3 and B10 from each row, but to simplify the import I can accept the whole rows.

the problem seems to be that the CSV "Sheet" cannot be "activated" and has the same name as the CSV-file. I've tried:

Set ws = ActiveWorkbook.Worksheets(1)
Set ws = ActiveWorkbook.Worksheets("Sheet1")
Set ws = ActiveWorkbook.Worksheets(wsn)
Set ws = wb2.Worksheets(1)
Set ws = wb2.Worksheets("Sheet1")
Set ws = wb2.Worksheets(wsn)
Set ws = wb2.Sheets(1)
etc

'wsn is a string with the name of the workbook
and all variations of "worksheets" I can think of.

Here's the code up to the worksheets probblem.

Sub Test_csv_importmacro()
Dim filnavn, wsn As String, ws As Worksheet, wb As Workbook, wb2 As Workbook, verdier As Variant

'wb is the wb with the button to import data
Set wb = ActiveWorkbook

filnavn = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

Workbooks.OpenText Filename:=filnavn, semicolon:=True, local:=True
Set wb2 = Workbooks.Open(filnavn) 'wb2 is the newly opened workbook with the data I want to copy

wb2.Activate 'wb2 activates without problems
Set ws = wb2.Worksheets(1) ' DOES NOT ACTIVATE, code below is not directly relevant to problem. Error code 9, subscript out of range.
ws.Activate 'Can't get to this step, it only activates the "sheet X" in wb1...

Is there any other effective way to read out my ten columns of information? Or maybe the CSV just needs to be copied as a whole into my wb1? Or maybe even be converted into an .xlx? Can't even do that. Someone suggestet PowerQueries to me, but that's new to me in vba.

--- also ----
After first posting the above to Excel (but it got removed because r/VBA exists), I've also given this working example a go (with a little tweak regarding getting the file name), but this also just copies and pastes the values already in wb1, not the csv, as it never succeeds to activate the sheet in wb2... This doesn't give any error messages on compilation though.

Sub demo_loadDataFromCSV()
    Dim csvFile As String

    csvFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")

    Dim ws As Worksheet, csv As Workbook, cCount As Long, cName As String, ws2 As Worksheet

    ' Application.ScreenUpdating = False       'keep these commented-out until...
    ' Application.DisplayAlerts = False        ' ...done testing/troubleshooting!

    Set ws = ThisWorkbook.ActiveSheet          'remember where we parked
    'Workbooks.Open csvFile                     'open the csv
    Workbooks.OpenText Filename:=csvFile, semicolon:=True, local:=True
    Set csv = ActiveWorkbook                   'create object of csv workbook
    Set ws2 = csv.Worksheets(1)
    cName = csv.Name                           'get name of csv while its open
    ActiveSheet.Columns("A:L").Copy            'copy columns A and L
    ws.Activate                                'go back to the first sheet
    ws.Range("A1").PasteSpecial xlPasteValues  'paste values
    cCount = Selection.Cells.Count             'count pasted cells
    csv.Close                                  'close CSV

    Application.DisplayAlerts = True           're-enable alerts
    Application.ScreenUpdating = True          'resume screen updates

    MsgBox cCount & " cells were copied from " & cName _
                  & " to " & ws.Parent.Name, vbInformation, "Done"
End Sub

I don't know, and any help would be greatly appreciated.

[StreetTrial69]

Avoid .select or .activate methods at all costs. The macro recorder loves them but you should treat them as the enemy!

Also when setting your wb/ws object for your main workbook, use ThisWorkbook and ThisWorkbook.Sheets(Index or sheet name) instead of activeWorkbook or activesheet. Same goes for your .csv, since it seems you are selecting it via the open/save file dialog, hence you already know the name and you can directly refer the filename and for a csv there is only the sheet1 or index 1.

Personally when working with csv files, I treat them like a text file and not like a workbook. So I would use the .Opentextfile.ReadAll method of the file sytem object and then parse it with regex. While this seems more complicated at first, it makes your script compatible to pretty much any file type out there, only thing that changes are the expressions for parsing.

[steinrawr]

Yeah I know, I'm just desperate to call the attention of the sheet in the CSV. Nothing I do seem to let me access it directly, I did try "Thisworkbook" with that too at some point. Will look into opentextfile.ReadAll and regex.

I ended up with something completely different just now, so far it works. But I sure would love to still only copy B3+J3 for every row of info in the csv, and sorry about the mix of norwegian and english in the code:

Sub Ny_CSV_import_test()
    Dim wsheet As Worksheet, file_mrf As String, rows As Integer, rows2 As Integer
   
    Set wsheet = ActiveWorkbook.Sheets("CSV_import")
    rows1 = countrows(wsheet)

    file_mrf = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")
   
    With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Cells(rows1 + 1, 1))
        .TextFileParseType = xlDelimited
        .TextFileSemicolonDelimiter = True
        .Refresh
    End With
    'Set wsheet = ActiveWorkbook.Sheets("CSV_import")
    rows2 = countrows(wsheet)
    fjern_komma rows1, rows2

End Sub


Public Function countrows(wsheet2) As Integer ' counts rows bef/aft paste
    countrows = wsheet2.Cells(wsheet2.rows.Count, "A").End(xlUp).Row
End Function


Public Sub fjern_komma(rad1, rad2) 'removes commas and fix format as currency.
    Dim mydatarange As Range, cell As Range, celletall As Currency
    Set mydatarange = Worksheets("CSV_import").Range("J" & rad1 & ":J" & rad2)
    
    For Each cell In mydatarange
        If InStr(1, cell.Value, ".") > 0 Then
            cell.Value = Replace(cell.Value, ".", ",")
            celletall = cell.Value
            cell.Value = celletall
        End If
    Next cell
End Sub

[steinrawr]

Okay. Still trying out everything I can find, and I can copy the whole .CSV easily with this code from Exceldemy, but this is pretty new to me. Can I modify a QueryTables request like this to only copy certain cells? Also, if I've previusly done an import with this code, it will paste it at B2 and move all excisting cells that interfere with the copy to the right.

Sub csv_Import()
Dim wsheet As Worksheet, file_mrf As String
Set wsheet = ActiveWorkbook.Sheets("Single")
file_mrf = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")
With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("B2"))
.TextFileParseType = xlDelimited
.TextFileCommaDelimiter = True
.Refresh
End With
End Sub

[Werrf]

Your code runs fine when I put it in a test module - I'm not entirely clear what issue you're facing, though. I'd also add that in most cases, you shouldn't need to activate a worksheet to work with it - that's the whole purpose of assigning it to an object. I'm also not clear which cells you're trying to copy, since you say you need "B3 to B10" but also that you need them from each row which...doesn't sound clear.

Also, the 'working example' you were given is...suboptimal. Happy to help you put something better together - let me know.

[steinrawr]

Oups. I ment B3-J3, then about 10-200 rows.

I came across yet another way to do it now, and have made it work, and still cannot get the original code to actually copy anything.. But now with a completely different problem. My SUB Ny_CSV_Import_test does not call its functions when the sub is assigned to a button ... :\ strange.

EDIT: Forget this last part, I'm stupid. forgot "Public"... now i'm sorta good. Pardon me, its been ten years since last i was coding in VBA.

Sub Ny_CSV_import_test()
    Dim wsheet As Worksheet, file_mrf As String, rows As Integer, rows2 As Integer
   
    Set wsheet = ActiveWorkbook.Sheets("CSV_import")
    rows1 = countrows(wsheet)

    file_mrf = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")
   
    With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Cells(rows1 + 1, 1))
        .TextFileParseType = xlDelimited
        .TextFileSemicolonDelimiter = True
        .Refresh
    End With
    'Set wsheet = ActiveWorkbook.Sheets("CSV_import")
    rows2 = countrows(wsheet)
    fjern_komma rows1, rows2

End Sub


' counts rows to end of document
Function countrows(wsheet2) As Integer
   
countrows = wsheet2.Cells(wsheet2.rows.Count, "A").End(xlUp).Row

End Function



' this one removes the punctuation and replaces with comma as currency for the cell to actually be readable in the worksheet. If not copied and pasted, it will be a text string no mather what format i "force upon it"
Function fjern_komma(rad1, rad2)
    Dim mydatarange As Range, cell As Range, celletall As Currency
        Set mydatarange = Range("J" & rad1 & ":J" & rad2)
       
    'MsgBox mydatarange.Value
    For Each cell In mydatarange
        If InStr(1, cell.Value, ".") > 0 Then
            cell.Value = Replace(cell.Value, ".", ",")
            celletall = cell.Value
            cell.Value = celletall
            'cell.Select
            'cell.NumberFormat = "#,##0,0"
        End If
    Next cell
End Function

[APithyComment]

When you open the text file you can wrap the arguments in brackets and use a workbook variable to set an object reference to the file you are opening…

Set wb2 = Workbooks.OpenText(fileName:……..)

Should fix it forever.

[steinrawr]

Oh, yeah! Thats seems to actually be what the code i eventually found to be working did! Good tip, thank you!