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]

Welcome to MrExcel.

First, are you typing in the file name including its extension? Second, are you typing in the folder path? Without the folder path Excel will look for the file name in the Application.DefaultFilePath folder and if it isn't there you'll get the error.

Rather than InputBox and typing in the full file name including its folder path, have a look at Application.GetOpenFilename and Application.FileDialog(msoFileDialogOpen) which allow you to browse to and select the file to be opened.

 

[ksmallfoot]

Welcome to MrExcel.

First, are you typing in the file name including its extension? Second, are you typing in the folder path? Without the folder path Excel will look for the file name in the Application.DefaultFilePath folder and if it isn't there you'll get the error.

Rather than InputBox and typing in the full file name including its folder path, have a look at Application.GetOpenFilename and Application.FileDialog(msoFileDialogOpen) which allow you to browse to and select the file to be opened.

John,

I like the idea of selecting a file much more, thank you!!! If I was going about copying a range of data from the selected file and transposing it, what do you think would be the best option?

I really appreciate the help.

-K

 

[John_w]

You want to copy a range from the selected file, but it's not clear where you want to transpose it to. Your code suggests to the same sheet in the selected file and the same top left cell.

Or do you want to copy from the selected file and transpose to the macro workbook? If so, to which sheet and cell?

 

[ksmallfoot]

You want to copy a range from the selected file, but it's not clear where you want to transpose it to. Your code suggests to the same sheet in the selected file and the same top left cell.

Or do you want to copy from the selected file and transpose to the macro workbook? If so, to which sheet and cell?

John,

Thank you for your response. My goal is to transpose the data on to a new sheet on the selected workbook. I have a new line of code that is closer to what I want, but I a still struggling to figure it out.

_________________________________
Dim PasteStart As Range
Dim ws2 As Worksheet


Set wb1 = ActiveWorkbook
Set PasteStart = [Sheet1!A1]




Sheet1.Select
Cells.Select
Selection.ClearContents


FileToOpen = Application.GetOpenFilename(Title:="Please choose a File to Open", _
FileFilter:="Report Files *.csv (*.csv),")


Set ws2 = wb2.Worksheets.Add(Type:=xlWorksheet)


If FileToOpen = False Then
MsgBox "No File Specified.", vbExclamation, "ERROR"
Exit Sub
Else
Set wb2 = Workbooks.Open(Filename:=FileToOpen)


For Each Sheet In wb2.Sheets
With Sheet.UsedRange
.Copy PasteStart
Set PasteStart = PasteStart.Offset(.Rows.Count)


End With
Next Sheet


End If

wb2.Close



End Sub

__________________________________


Another problem with this code is that I do not want to close wb2 at the end of the code.

I am very much so a rookie, so any help is appreciated.

Thanks

-K

 

[John_w]

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

 

[ksmallfoot]

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

John,

This works great! I really really appreciate the help. Thank you so much.

-K

 

[ksmallfoot]

I am back! I am struggling to pull the data from the files that I want. I now have my selected file opening and transposing on a new page, just as I wanted. But now I want to take the mean, mode, and standard deviation of that transposed data for each column, or "channel" on that file. What would be a efficient way to do that? I find myself trying very basic things but getting stuck. Hope you can help!

Thanks

-K

 

[John_w]

Have you tried recording a macro whilst you enter the formulas for the first column and drag across (autofill) the other columns?

 

[ksmallfoot]

The problem with this is then the macro is linked to that new file instead of the "program" I have been creating, which loses its purpose. The program I am creating will be used for over 300 of these files, with 64 columns in each file that needs to be analyzed. By analyzed I mean pulling out the mean, mode, standard deviation, a logarithmic histogram plot, also having the height, position, and width of the peaks from that histogram. Frankly, I don't even know if this is possible! I am just a confused engineering student trying to impress my professor, haha.