How to open a file using inputbox to type file name

[ksmallfoot]

Hello!

I am quite new to VBA, but I am trying to create a program that allows me to push a button that asks for a file name, and after entering the file name, it opens up the file I requested. I have attempted this here,

______________________________________


Sub Start()
Filename = InputBox("File Name:")


Workbooks.Open Filename


Range("A3:SU11").Cut


Range("A3:SU11").PasteSpecial.Transpose




End Sub

______________________________________________

when I ran this code, it said that it could not find a file with that name. I am not sure what I am doing wrong!

I hope someone can help, thank you!


-K

 

[John_w]

You are correct in that a recorded macro, as I suggest, is specific to the sheet you perform it on, e.g. the formulas will be put in specific cells. However the code can be edited to put the formulas in cells below the data, depending on the number of rows, and then it should work for any data.

Try this macro, which transposes the data and puts the formulas for mean, mode and standard deviation in cells below the data in each column.

Public Sub Copy_and_Transpose_CSV_Used_Range_with_Formulas()

    Dim inputFile As Variant, outputFile As String
    Dim wb As Workbook, newWs As Worksheet
    Dim copyRange As Range
    Dim lastRow As Long, lastCol As Long
    
    inputFile = Application.GetOpenFilename(FileFilter:="Report Files *.csv (*.csv),*.csv", Title:="Please choose a .csv file to open", MultiSelect:=False)

    If inputFile <> False Then
    
        'Define name of output .xlsx file
        
        outputFile = Replace(inputFile, ".csv", " TRANSPOSED.xlsx", compare:=vbTextCompare)
    
        'Open .csv input file and transpose data to a new sheet starting at cell A1
        
        Set wb = Workbooks.Open(inputFile)
        Set copyRange = wb.Worksheets(1).UsedRange
        Set newWs = wb.Worksheets.Add(After:=wb.Worksheets(wb.Worksheets.Count))
        copyRange.Copy
        newWs.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
        Application.CutCopyMode = False
        
        'Put formulas for mean, mode and standard deviation below data in each column
        
        With newWs
            lastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
            lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
            .Cells(lastRow + 1, 1).Formula = "=AVERAGE(A1:A" & lastRow & ")"
            .Cells(lastRow + 2, 1).Formula = "=MODE.SNGL(A1:A" & lastRow & ")"
            .Cells(lastRow + 3, 1).Formula = "=STDEV.P(A1:A" & lastRow & ")"
            .Cells(lastRow + 1, 1).Resize(3, 1).AutoFill Destination:=.Cells(lastRow + 1, 1).Resize(3, lastCol), Type:=xlFillDefault
        End With
        
        'Save output .xlsx file
        
        Application.DisplayAlerts = False
        wb.SaveAs Filename:=outputFile, FileFormat:=xlOpenXMLWorkbook
        Application.DisplayAlerts = False

        'Close output .xlsx file
        
        'wb.Close False
    End If

End Sub

 

[ksmallfoot]

John,

This code works pretty well, I will make some adjustments for aesthetic reasons but I think it is a great start. Thank you so much for helping me out. I really really appreciate it!

-K

 

[Muhammad Mateen]

See if this does what you want. It opens the selected .csv file, copies the used range (in worksheets(1), since .csv files only have 1 'sheet'), and transposes it to cell A1 in a newly added sheet. You'll then have to save the updated .csv file as an Excel workbook.

Public Sub Copy_and_Transpose_CSV_Used_Range()

    Dim file As Variant
    Dim csvWb As Workbook, newWs As Worksheet
    Dim copyRange As Range
   
    file = Application.GetOpenFilename(FileFilter:="Report Files *.csv (*.csv),*.csv", Title:="Please choose a .csv file to open", MultiSelect:=False)

    If file <> False Then
        Set csvWb = Workbooks.Open(file)
        Set copyRange = csvWb.Worksheets(1).UsedRange
        Set newWs = csvWb.Worksheets.Add(after:=csvWb.Worksheets(csvWb.Worksheets.Count))
        copyRange.Copy
        newWs.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    End If

End Sub

I tried the same code but gave an error. The only change i did, was for multiselect true. Pl help

 

[John_w]

MultiSelect:=True is a significant change because then Application.GetOpenFileName returns an array of files instead of one file.

This should handle multiple files:

    Dim inputFiles As Variant
    inputFiles = Application.GetOpenFilename(FileFilter:="Report Files *.csv (*.csv),*.csv", Title:="Please choose one or more .csv files to open", MultiSelect:=True)
    
    If IsArray(inputFiles) Then

        For Each inputFile In inputFiles
            'Define name of output .xlsx file           
            outputFile = Replace(inputFile, ".csv", " TRANSPOSED.xlsx", compare:=vbTextCompare)           
            'Continue as in code #11
        Next

    End If

But please start a new thread if you require other changes.