.csv open/manipulate/close

[dv512263]

Hello,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I’m trying to build a spreadsheet that is very data intensive. In order to manage size I’d like minimize the amount of data and calculations I do within the workbook. I simply need to open a .csv file, manipulate the file, copy and paste the manipulated data and close the .csv file. I’ve been unable to find code to open and close .csv files. Any help would be much appreciated. <o></o>

 

[AMAS]

Hi,

This is put you on the right direction. I have compiled this to open CSV files. There is a place in it for you to modify according to your needs.

Sub CSVOpenFiles()

Dim screenUpdateState       As Variant
Dim statusBarState          As Variant
Dim eventsState             As Variant
Dim fso                     As Object
Dim fPath                   As String
Dim myFolder, myFile
Dim wb                      As Workbook
Dim SavePath                As String
Dim I, x                    As Integer


' Turn off some Excel functionality so your code runs faster
    screenUpdateState = Application.ScreenUpdating
    statusBarState = Application.DisplayStatusBar
    eventsState = Application.EnableEvents

    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.EnableEvents = False

' Use File System Object to choose folder with files
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        .InitialFileName = "C:\Temp\"               ' Default path - Change as required
        .Title = "Please Select a Folder"
        .ButtonName = "Select Folder"
        If .SelectedItems.Count > 0 Then fPath = .SelectedItems(1) & "\"
            If .SelectedItems.Count = 0 Then
                MsgBox "No folder was chosen." & vbLf & vbLf & "Please try again.", vbExclamation, "User Cancelled."
                Exit Sub
            End If
    End With

' Open each file consequently
        Set myFolder = fso.GetFolder(fPath).Files
            For Each myFile In myFolder
                If LCase(myFile) Like "*.csv" Then
                    For I = 1 To x

' Perform tasks with each file
                        Set wb = Workbooks.Open(myFile)
                        With wb.Worksheets(1)
'***************************************************************************************************************
                            
                            
                            'Add code to do something with each file
                        
                        
'***************************************************************************************************************
                        End With
                        
                        ' Close file
                        wb.Close False

' Loop through all files in folder
                    Next I
                End If
            Next myFile

'clean up
    myFile = vbNullString
    I = 1

' Turn Excel functionality back on
    Application.ScreenUpdating = screenUpdateState
    Application.DisplayStatusBar = statusBarState
    Application.EnableEvents = eventsState

End Sub

Hope this helps.

AMAS

 

[dv512263]

thanks for your quick response AMAS.

I should have added that the .csv does not exist on my C: drive - it must be downloaded/opened, manipulated, copy/paste and closed without saving from the following website:

http://mis.nyiso.com/public/csv/damlbmp/20110806damlbmp_zone.csv

thanks!

 

[dv512263]

AMAS, anybody else?

 

[taurean]

In the cell A1, paste the URL address which you have stated here and then test this code.

'From All API
'example by Matthew Gates ([EMAIL="Puff0rz@hotmail.com"]Puff0rz@hotmail.com[/EMAIL])
'http://www.vbaexpress.com/forum/showthread.php?t=29333
Dim sFlName As String
Dim vFlName As Variant
Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long
Public Sub DownloadCSV()
'Getting the filename
vFlName = Split(Range("A1").Value, "/")
sFlName = "C:\" & vFlName(UBound(vFlName))
'Calling Matthew Gates method
DownloadFile Range("A1").Value, sFlName
'Download is complete so lets get it copied
Call CopyFile
End Sub
Private Function DownloadFile(URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function
Private Sub CopyFile()
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
Workbooks.Open sFlName
Workbooks(vFlName(UBound(vFlName))).Sheets(1).Copy After:=wbThis.Worksheets(Sheets.Count)
'We don't need it now since we have copied the data
Workbooks(vFlName(UBound(vFlName))).Close sFlName
Kill sFlName
End Sub

 

[dv512263]

taurean,

this works well, thank you. Is it possible to insert the link directly into vFIName? Ideally, I would like to replace the date component in the link w/ a declared date, for example, "Date1 = Format(Sheet1.Range("Date1"),"YYYYMMDD") using " & Date1 & " within the link. I'd prefer this over building links in worksheets. If this is not possible, is there a way to build a hyperlink in a sheet with a dynamic date?

 

[dv512263]

I should also ask where I will insert the code to manipulate the file (apply filters, copy/paste only specific sections). After "Workbooks.Open sFlName" ? I should be able to delete "Workbooks(vFlName(UBound(vFlName))).Sheets(1).Copy After:=wbThis.Worksheets(Sheets.Count)" entirely if I don't want to create a new worksheet with all the data, right?

 

[taurean]

taurean,

this works well, thank you. Is it possible to insert the link directly into vFIName? Ideally, I would like to replace the date component in the link w/ a declared date, for example, "Date1 = Format(Sheet1.Range("Date1"),"YYYYMMDD") using " & Date1 & " within the link. I'd prefer this over building links in worksheets. If this is not possible, is there a way to build a hyperlink in a sheet with a dynamic date?

If you mean:

sFlName = "[URL]http://mis.nyiso.com/public/csv/damlbmp/[/URL]" & Format(Range("A1").Value, "YYYYMMDD") & "damlbmp_zone.csv"

In Cell A1 insert following formula:
=TODAY()
It will return current date. Is this what you want?

I should also ask where I will insert the code to manipulate the file (apply filters, copy/paste only specific sections). After "Workbooks.Open sFlName" ? I should be able to delete "Workbooks(vFlName(UBound(vFlName))).Sheets(1).Copy After:=wbThis.Worksheets(Sheets.Count)" entirely if I don't want to create a new worksheet with all the data, right?

Yes. If you are not sure at some step then use F8 (stepping through code) and change it where you want.

 

[dv512263]

that's exactly what I needed to know, your help is much appreciated!

 

[taurean]

Good Night (Does the forum ever sleep?). Have a nice weekend!