Using filedialog in VBA

Question

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

Using the Format function, something like Format( 50000, "#,##0.0") where 50000 can be a variable or a cell. — Wookies-Will-Code, Feb 20 '18 at 14:34
I have __no problem__ running your code. Please show us a few lines from your input. Note: you seem to be working in Dutch. Are you sure the International Settings match your input? (So please show us your input). Try seting your International settings in Control Panel to US. — Paul Ogilvie, Feb 20 '18 at 14:55

score 0 · Answer 1 · answered Feb 20 '18 at 14:36

0

I can think of two ways to do this. One would be to format the cells after you paste the data using VBA. However I would recommend you to use the easier option- paste as value. Here is what the syntax would look like

Sheets(1).Range("B23:B" & Range("B23").End(xlDown).Row).Copy
ActiveSheet.Range("").PasteSpecial xlPasteValues 'Enter the range as you need

Pasting as values would help you keep all the formatting. Hope this helps.

answered Feb 20 '18 at 14:36

shash

246
1
7

It doesn't work with PasteSpecial because when I copy from the source it's text and not numbers. The problem roots from the filedialog. I can copy and paste fine without filedialog – Mattias Feb 20 '18 at 16:11
Okay, then see if using this for your range works `ActiveSheet.Range("").NumberFormat = "General"` – shash Feb 20 '18 at 17:50

sporc · Answer 2 · 2018-02-20T16:16:41.107

0

You can change the NumberFormat of a Range to General. See: https://msdn.microsoft.com/en-gb/vba/excel-vba/articles/range-numberformat-property-excel

It is not recommend to use ThisWorkbook or Active. You can get further information to this topic here: How to avoid using Select in Excel VBA

Why do you say i*1? I changed it to simply i

This should do it for you:

Dim wb_main As Workbook
Dim wb_source As Workbook

Set wb_main = ThisWorkbook

...

For i = 1 To fd.SelectedItems.Count
    Set wb_source = Workbooks.Open(Filename:=fd.SelectedItems(i))

    wb_source.Sheets(1).Range("B23:B" & Range("B23").End(xlDown).Row).Copy
    'Application.DisplayAlerts = False
    wb_source.Close SaveChanges:=False

    With wb_main.Sheets("Blad2")
        .Cells(1, i).PasteSpecial xlPasteValues
        .Columns(i).NumberFormat = "General"
    End With

    'Application.DisplayAlerts = True

Next i

Why you use Application.DisplayAlerts = False? If you don't want Excel to ask if changes should be changed you can add SaveChanges:=False to the close operation: wb_source.Close SaveChanges:=False

Hope this helps

edited Feb 20 '18 at 16:16

answered Feb 20 '18 at 14:48

sporc

387
1
4
14

First: I used i*1 because i changed it to i*10 because of other reasons. I know it looks silly they it looks now. Second: I Used "Application.DisplayAlerts = False" because Excel keeps asking me if i want to save data to clipboard. There are a lot of cells that I'm copying. I tried your code but I get an error saying that the object doesn't support the property or method at the line ".Cells(1, i).Paste" – Mattias Feb 20 '18 at 16:08
I edited the anwser. I replaced `Paste` with `PasteSpecial xlPasteValues`. Now it should work – sporc Feb 20 '18 at 16:17
As I said before I'm not that used to VBA but It doesn't work with PasteSpecial either. I get the error 1004 "PasteSpecial-method in Range-class Failed". – Mattias Feb 27 '18 at 09:37

score 0 · Accepted Answer · edited Apr 19 '18 at 13:21

0

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'

edited Apr 19 '18 at 13:21

Stephen Rauch

47,830
31
106
135

answered Apr 19 '18 at 12:58

Mattias

9
6

Using filedialog in VBA

3 Answers3