Insert Data from a column (B2:B45) from csv file, via Dialog box opening from standard excel file ( xlsm )

Omarom

New Member
Joined
Oct 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I want to Insert Data from a column (B2:B45) from csv file,directly via Dialog box (( to choose csv file )) , which opening from the standard excel file ( xlsm )
the CSV file called : StudentClass.CSV
I've tried this :

Private Sub importv_Click()

Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Title = "Select a CVS File"
.Filters.Add "CSV", "*.csv", 1
.AllowMultiSelect = False
Dim fileName As String
If .Show = True Then
fileName = .SelectedItems(1)
End If
End With

' import
If fileName <> "" Then
Range("B2:B45").Select
Selection.ClearContents
Range("B2").Select
----------------------------------------
Now, I don't know How can I get data from column (B2:B45) from ( StudentClass.csv ) and insert it just i press OK from dialog box..
Any one can help me ?
 

Attachments

  • بدون عنوان.png
    بدون عنوان.png
    23.4 KB · Views: 4

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Puertorekinsam

Active Member
Joined
Oct 8, 2005
Messages
287
Try this
VBA Code:
Private Sub importv_Click()

Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Title = "Select a CVS File"
.Filters.Add "CSV", "*.csv", 1
.AllowMultiSelect = False
Dim fileName As String
If .Show = True Then
fileName = .SelectedItems(1)
End If
End With

' import
If fileName <> "" Then
Dim CV As Workbook
Set CV = Application.Workbooks.Open(fileName)

Lastrow = CV.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
Me.Range(Me.Cells(2, 2), Me.Cells(2, 45)).Copy CV.Sheets(1).Range(CV.Sheets(1).Cells(Lastrow + 1, 1), CV.Sheets(1).Cells(Lastrow + 44, 1))
CV.Close SaveChanges:=True
End If


End Sub
 

Omarom

New Member
Joined
Oct 21, 2020
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Try this
VBA Code:
Private Sub importv_Click()

Dim fd As Office.FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Title = "Select a CVS File"
.Filters.Add "CSV", "*.csv", 1
.AllowMultiSelect = False
Dim fileName As String
If .Show = True Then
fileName = .SelectedItems(1)
End If
End With

' import
If fileName <> "" Then
Dim CV As Workbook
Set CV = Application.Workbooks.Open(fileName)

Lastrow = CV.Sheets(1).Cells.SpecialCells(xlCellTypeLastCell).Row
Me.Range(Me.Cells(2, 2), Me.Cells(2, 45)).Copy CV.Sheets(1).Range(CV.Sheets(1).Cells(Lastrow + 1, 1), CV.Sheets(1).Cells(Lastrow + 44, 1))
CV.Close SaveChanges:=True
End If


End Sub
thank you for your fast reply
and sorry for my delayed reply :)
there is a Debug message on "Me", something goes wrong
However I found the solution here :

Private Sub get_data_from_file_62()

Application.ScreenUpdating = False
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.csv*),*csv*")

If FileToOpen <> False Then

Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Sheets(1).Range("A2:A45").Copy
ThisWorkbook.Activate

Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
:=False, Transpose:=False

OpenBook.Close False
End If
 

Puertorekinsam

Active Member
Joined
Oct 8, 2005
Messages
287
glad you got it to work. the 'me code that gave you the error was all dependent on 'where' the code lived. I had it inside the code for the page I was working on, you likely have it in a separated module
 

Watch MrExcel Video

Forum statistics

Threads
1,127,321
Messages
5,624,003
Members
416,004
Latest member
reitz1

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
Top