• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

import csv file without changing the name of the sheet

Hi, I have the following code which allows the user to open a file. But I only want this file to be one sheet in a workbook. It seems it opens the file and then the name of the workbook becomes that file's name. Also, I don't want the current name of the worksheet to change when I open the file. Can anyone suggest changes?
Thanks.
Dim fileName
fileName = Application.GetOpenFilename("Comma Separated Values (*.csv),*.csv")
    If fileName <> "False" Then
Workbooks.Open fileName, Format:=2
End If

Open in new window

0
willie108
Asked:
willie108
  • 6
  • 5
1 Solution
 
SteveCommented:
This should get you going...
Sub CSV_Import()
Dim ws As Worksheet, strFile As String
 
Set ws = ActiveWorkbook.Sheets("Sheet1") 'set to current worksheet name
 
strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please selec text file...")
 With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
 End With
   ws.Name = "testing"
 End Sub

Open in new window

0
 
willie108Author Commented:
Excellent. Thanks! One more, if I want to write starting at row 2 is there an easy way to do it?
0
 
willie108Author Commented:
Excellent. Thanks! One more, if I want to write to the sheet1 starting at row 2 of sheet1 is there a way to do it?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SteveCommented:
Change the Destination:=ws.Range("A1")) to Destination:=ws.Range("A2"))
0
 
willie108Author Commented:
Thanks again. It seems that I am overwriting the contents of the first row. Is there away to avoid that?
0
 
willie108Author Commented:
actually it is not overwriting it. It is pushing it to the right.
0
 
SteveCommented:
we can program the first row to fill after the import:

Sub CSV_Import()
Dim ws As Worksheet, strFile As String
 
Set ws = ActiveWorkbook.Sheets("Sheet1") 'set to current worksheet name
 
strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please selec text file...")
 With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A2"))
     .TextFileParseType = xlDelimited
     .TextFileCommaDelimiter = True
     .Refresh
 End With
   ws.Name = "testing"

with ws
.cells(1,1) = "Column1"
.cells(1,2) = "Column2"
.cells(1,3) = "Column3"
.cells(1,4) = "Column4"
.cells(1,5) = "Column5"
end with

 End Sub 

Open in new window

0
 
SteveCommented:
you may want to add the following line after the ws.Set line

ws.Cells.ClearContents

Open in new window

or
ws.Rows(1).ClearContents

Open in new window

You may note that the data created is a query, so you should be able to refresh data in the sheet from the text file by right click refresh within the data. Providing you keep the same text file name.
0
 
willie108Author Commented:
Thanks. Do  you mean after
Set ws = ActiveWorkbook.Sheets("Sheet1") 'set to current worksheet name
0
 
SteveCommented:
yes this will clear the sheet before adding the new table.
0
 
willie108Author Commented:
Thanks. Really helpful.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now