Locate CSV file with Open File msg box and Import Columns from CSV to worksheet

T

tahir573

New Member
Joined
Dec 31, 2008
Messages
44
hello all,

i need your help in excel vba, Actually i've downloaded Tracking report of my SPO and this report is on 4-5 csv files (1 file per week). I have to gather data from these 4 files into one Worksheet. I have to do this with command button on my worksheet; with Open file dialogue box (i want to locate the csv files).

CSV files are something like that (I need only first 4 columns):

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:97px;"><col style="width:100px;"><col style="width:141px;"><col style="width:273px;"><col style="width:113px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>Name</td><td> MSISDN</td><td> Date</td><td> Location</td><td> MapLink </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>M. Younus Safi</td><td> "923***550577"</td><td> "2011-10-07 20:36:18"</td><td> "ACB Tower Badragah Chakwal. Badraga"</td><td> "http://asdf.com" </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>M. Younus Safi</td><td> "923***550577"</td><td> "2011-10-07 19:36:26"</td><td> "ACB Tower Badragah Chakwal. Badraga"</td><td> "http://asdf.com" </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>M. Younus Safi</td><td> "923***550577"</td><td> "2011-10-07 16:39:58"</td><td> "ACB Tower Badragah Chakwal. Badraga"</td><td> "http://asdf.com" </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>M. Younus Safi</td><td> "923***550577"</td><td> "2011-10-07 15:37:23"</td><td> "ACB Tower Badragah Chakwal. Badraga"</td><td> "http://asdf.com" </td></tr><tr style="height:17px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>M. Younus Safi</td><td> "923***550577"</td><td> "2011-10-07 14:37:05"</td><td> "ACB Tower Badragah Chakwal. Badraga"</td><td> "http://asdf.com" </td></tr></tbody></table>

and my Worksheet is like that (With a command Button):

I want to import first 3 columns of csv file on first 3 columns on my worksheet; leave 2 columns blank and then import the 4th column... now for example i have done importing data from 1st csv file and the data is on 50 rows.. i click the command button again, locate the 2nd csv file.. do the same thing (import 1st 3 columns, 2 blank, then 4th) --from 51st Row-- and so on... hope u understand.. :(

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:215px;"><col style="width:110px;"><col style="width:110px;"><col style="width:75px;"><col style="width:75px;"><col style="width:355px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td style="font-weight:bold; text-align:center; ">Name</td><td style="font-weight:bold; text-align:center; ">MSISDN</td><td style="font-weight:bold; text-align:center; "> Dated</td><td style="font-weight:bold; text-align:center; ">Blank</td><td style="font-weight:bold; text-align:center; ">Blank</td><td style="font-weight:bold; text-align:left; "> Location</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:16px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>
Can anyone help me? plz..
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
Make sure that sheet in which to import the data is the active sheet, then try the following macro. Note that it allows the user to select one or more files.

Code: 
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] wksDest [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] wksSource [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] rSourceRng1 [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] rSourceRng2 [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FileNames [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] SourceRowCount [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]

    [color=darkblue]Set[/color] wksDest = ActiveWorkbook.ActiveSheet
    
    [color=darkblue]With[/color] wksDest
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    [color=darkblue]End[/color] [color=darkblue]With[/color]

    FileNames = Application.GetOpenFilename( _
                                                            filefilter:="Comma Delimited (*.csv), *.csv", _
                                                            Title:="Please select one or more files...", _
                                                            MultiSelect:=True)
                                                            
    [color=darkblue]If[/color] [color=darkblue]Not[/color] IsArray(FileNames) [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](FileNames) [color=darkblue]To[/color] [color=darkblue]UBound[/color](FileNames)
        [color=darkblue]Set[/color] wksSource = Workbooks.Open(FileNames(i)).Worksheets(1)
        [color=darkblue]With[/color] wksSource.UsedRange
            [color=darkblue]Set[/color] rSourceRng1 = .Offset(1, 0).Resize(.Rows.Count - 1, 3)
            [color=darkblue]Set[/color] rSourceRng2 = .Offset(1, 3).Resize(.Rows.Count - 1, 1)
            rSourceRng1.Copy Destination:=wksDest.Cells(NextRow, "A")
            rSourceRng2.Copy Destination:=wksDest.Cells(NextRow, "F")
            SourceRowCount = rSourceRng1.Rows.Count
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        wksSource.Parent.Close savechanges:=[color=darkblue]False[/color]
        NextRow = [color=darkblue]Next[/color]Row + SourceRowCount
    Next i
        
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
End [color=darkblue]Sub[/color]
[/font]
 
Upvote 0
Thank you very much Domenic :) it works very fine... but my dear please do me a favor... my worksheet starts from A5..

<table style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:215px;"><col style="width:110px;"><col style="width:145px;"><col style="width:75px;"><col style="width:75px;"><col style="width:355px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>
</td><td>A</td><td>B</td><td>C</td><td>D</td><td>E</td><td>F</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">2</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">3</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">4</td><td style="font-weight:bold; text-align:center; ">Name</td><td style="font-weight:bold; text-align:center; ">MSISDN</td><td style="font-weight:bold; text-align:center; "> Dated</td><td style="font-weight:bold; ">
</td><td style="font-weight:bold; ">
</td><td style="font-weight:bold; text-align:left; "> Location</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">5</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">6</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">7</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">8</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">9</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">10</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr><tr style="height:20px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">11</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td><td>
</td></tr></tbody></table>

What should i do now ??

should i change this code

With wksDest
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With

with this one ??

With wksDest
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 5
End With
 
Upvote 0
Try the following instead...

Code: 
[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] wksDest [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] wksSource [color=darkblue]As[/color] Worksheet
    [color=darkblue]Dim[/color] rSourceRng1 [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] rSourceRng2 [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FileNames [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] SourceRowCount [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] NextRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]

    FileNames = Application.GetOpenFilename( _
        Filefilter:="Comma Delimited (*.csv), *.csv", _
        Title:="Please select one or more files...", _
        MultiSelect:=True)
                                                            
    [color=darkblue]If[/color] [color=darkblue]Not[/color] IsArray(FileNames) [color=darkblue]Then[/color] [color=darkblue]Exit[/color] [color=darkblue]Sub[/color]
    
    [color=darkblue]Set[/color] wksDest = ActiveWorkbook.ActiveSheet
    
    [color=darkblue]With[/color] wksDest
        [color=darkblue]With[/color] .Range("A4:C4")
            .Value = Array("Name", "MSISDN", "Date")
            .Font.Bold = [color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        [color=darkblue]With[/color] .Range("F4")
            .Value = "Location"
            .Font.Bold = [color=darkblue]True[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
    [color=darkblue]End[/color] [color=darkblue]With[/color]

    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    [color=darkblue]For[/color] i = [color=darkblue]LBound[/color](FileNames) [color=darkblue]To[/color] [color=darkblue]UBound[/color](FileNames)
        [color=darkblue]Set[/color] wksSource = Workbooks.Open(FileNames(i)).Worksheets(1)
        [color=darkblue]With[/color] wksSource.UsedRange
            [color=darkblue]Set[/color] rSourceRng1 = .Offset(1, 0).Resize(.Rows.Count - 1, 3)
            [color=darkblue]Set[/color] rSourceRng2 = .Offset(1, 3).Resize(.Rows.Count - 1, 1)
            rSourceRng1.Copy Destination:=wksDest.Cells(NextRow, "A")
            rSourceRng2.Copy Destination:=wksDest.Cells(NextRow, "F")
            SourceRowCount = rSourceRng1.Rows.Count
        [color=darkblue]End[/color] [color=darkblue]With[/color]
        wksSource.Parent.Close savechanges:=[color=darkblue]False[/color]
        NextRow = [color=darkblue]Next[/color]Row + SourceRowCount
    Next i
        
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
End [color=darkblue]Sub[/color]
[/font]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
695
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
608
mumps
mumps
E
  • Question Question
Adding a custom message.
Replies
1
Views
675
Micron
M
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A

Forum statistics

Threads
1,225,071
Messages
6,182,692
Members
453,132
Latest member
nsnodgrass73

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top