Trying to import CSV into Excel with VB

[tsunamisurfer]

Hi,

I'm a bit new to VB and I'm having trouble importing a CSV into a workbook.

Actually importing a known CSV is relatively easy but the CSV that I wish to import changes names each time (date-stamped).

So what I'd like to to get VB to bring up the Open File window, that I may be able to select the appropriate file at the appropriate location.

I've tried with this line of code below but it will not actually open the file after clicking the open button.

varFile = Application.GetOpenFilename

Can anyone help? Any help is appreciated.

The rest of my code below.....but the macro already specifies which filename, I would like the user to specify the CSV file

Sub CSV_Import()
'
' CSV_Import Macro
'

'
Sheets("Bucket Totals&quot.Select
Cells.Select
Selection.Clear
Range("A1&quot.Select
Workbooks.Open FileName:="D:\Projex\VBA\AFS2_CUSTODY_BALANCES_01072003.CSV"
Cells.Select
Selection.Copy
ActiveWindow.Close
ActiveSheet.Paste
Cells.Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.ShrinkToFit = False
.MergeCells = False
End With
Cells.EntireColumn.AutoFit
Rows("1:5&quot.Select
Range("A5&quot.Activate
Selection.Delete Shift:=xlUp
End Sub

Regards

 

[robcarr]

this should do the trick,

ddate = Format(Date - 1, "ddmmyyyy&quot
fname = "D:\Projex\VBA\AFS2_CUSTODY_BALANCES_" & ddate & ".csv"
Workbooks.Open Filename:=fname

hope it helps.

Rob.

Thanks Rob.

 

[robcarr]

if you want to bring up a box so the user can select the file to open you will need the following coding.

Option Explicit

Private Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" ( _
pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Sub custodybalance()
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim strFilter As String

OpenFile.lStructSize = Len(OpenFile)
'// Define your wildcard string here
'// Note we pad the strings with Chr(0)
'// This indicates an end of a string
strFilter = "CSV Files(*.csv)" & Chr(0) & "*.csv" & Chr(0)

With OpenFile
.lpstrFilter = strFilter
.nFilterIndex = 1
.lpstrFile = String(257, 0)
.nMaxFile = Len(.lpstrFile) - 1
.lpstrFileTitle = .lpstrFile
.nMaxFileTitle = .nMaxFile
.lpstrInitialDir = "D:\Projex\VBA\"
.lpstrTitle = "Select Current Custody Balance sheet"
.flags = 0
End With

lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
'// User cancelled Do your thing
MsgBox "User cancelled"
Else
'// Do your thing
'MsgBox "User selected" & ":=" & OpenFile.lpstrFile
'// So to Open the File use above Format
'// OpenFile gets part of OpenFile.lpstrFile
'// to the left of first Chr(0)
'// eg:
'//
Dim FileToOpen As String
FileToOpen = Application.WorksheetFunction.Clean(OpenFile.lpstrFile)
Workbooks.Open FileToOpen
End If

End Sub

Thanks Rob.