Search code examples
excelvbacopy-pastefiledialog

Using filedialog in VBA

I usually just google and eventually find an answer but this time I am really stuck. I am not that familiar with VBA and how it works but got the basic idea.

What I am trying to do is to collect data from a number of .lvm files and put it in a graph. The .lvm files are text files with tab-separated columns and they contain text as well as decimal numbers.

I want to select several files from a filedialog and copy one column from the file and put it in a graph. I am able to copy the column but when I copy the value as text instead of number. When I open the file in Excel normally the values have format "General" and I can make graphs from it. But when the file gets opened by filedialog something is happening with the format. My code looks like this (so far):

Private Sub CommandButton1_Click()

Dim fd As FileDialog
Dim FileChosen As Variant
Dim FileName As String
Dim i As Integer
Set fd = Application.FileDialog(msoFileDialogFilePicker)
'use the standard title and filters, but change the
'initial folder
fd.InitialView = msoFileDialogViewList
'allow multiple file selection
fd.AllowMultiSelect = True

FileChosen = fd.Show

If FileChosen = -1 Then
'open each of the files chosen

    For i = 1 To fd.SelectedItems.Count
        Workbooks.Open fd.SelectedItems(i)      

        Sheets(1).Range("B23:B" & Range("B23").End(xlDown).Row).Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.Close
        ThisWorkbook.Sheets("Sheet2").Activate
        Sheets("Sheet2").Select
        Sheets("Sheet2").Cells(1, i * 1).Select

        ActiveSheet.Paste

        Application.DisplayAlerts = True
    Next i

End If

End Sub

My source looks something like this: "Lots of text until row 23" 0,1 0,2 0,4 0,5 0,7 0,8 0,9 0,2 0,5 0,2 0,1 0,1 0,1 0,2 0,4 0,5 0,7 0,8 0,9 0,2 0,5 0,2 0,1 0,1 0,1 0,2 0,4 0,5 0,7 0,8 0,9 0,2 0,5 0,2 0,1 0,1 0,1 0,2 0,4 0,5 0,7 0,8

There are a lot more rows in the original file and it has five digits after the decimal separator

Solution

  • I found out myself what was wrong! The problem was actually caused by workbooks.open. Somehow it didn't respond to delimiters and therefore the source file was imported in a wrong way. I solved it by using workbooks.OpenText instead.

    Workbooks.OpenText FileName:= _
fd.SelectedItems(i), DataType:=xlDelimited, Local:=True
Set wb_source = ActiveWorkbook'