CODEWITHSUNDEEP
vbaexcelcsvworksheet
Jon Snow
Jon Snow

Reputation: 31

Open CSV file in background and retrieve worksheet name

My VBA project consists on cleaning databases.

I'm using a Userform, and this is the code i wrote till now.

I have been successful at importing a file, but i still can't perform actions on it.

  1. I need you guys to show me how can i get the workbook name and worksheets of the .csv file i just imported to start assigning actions referring to the database by its workbook name and worksheet name.
  2. I'd also appreciate it if you can show me how to keep my userform on top, or how to import the .csv file without displaying it

code

Dim fNameAndPath As Variant

Private Sub importedworkbook_Click()
MsgBox "imported workbook : " & _
fNameAndPath
End Sub


Private Sub Importbutton_Click()

fNameAndPath = Application.GetOpenFilename(FileFilter:="CSV Files Only (*.CSV), *.XLS", Title:="Select File To Be Opened")
If fNameAndPath = False Then Exit Sub
Workbooks.Open Filename:=fNameAndPath
End Sub

Upvotes: 2

Views: 2141

Answers (2)

brettdj
brettdj

Reputation: 55692

You can open a second instance of Excel to work with the hidden file

    Dim objExcel As Excel.Application
    Dim wb As Workbook
    fNameAndPath = Application.GetOpenFilename(FileFilter:="CSV Files Only (*.CSV), *.XLS", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub

    Set objExcel = New Excel.Application
    Set wb = objExcel.Workbooks.Open(fNameAndPath)
    Set wsCSV = wb.Worksheets(1)

    MsgBox wsCSV.Name
    ' do stuff

    'close second instance  
    wb.Close False
    objExcel.Quit
    Set objExcel = Nothing

Upvotes: 0

chris neilsen
chris neilsen

Reputation: 53136

Best to use declared variables to reference the opened CSV sheet

Private wsCSV as Worksheet

Private Sub Importbutton_Click()
    Dim wb as Workbook
    fNameAndPath = Application.GetOpenFilename(FileFilter:="CSV Files Only (*.CSV), *.XLS", Title:="Select File To Be Opened")
    If fNameAndPath = False Then Exit Sub
    Set wb = Workbooks.Open(Filename:=fNameAndPath)
    Set wsCSV = wb.Worksheets(1) ' A csv file will only ever have one sheet
End Sub

Variable wsCSV now refers to the CSV sheet, and can be referenced in other code in the UserForm module

Upvotes: 1

Related Questions