Macro for CSV import to specified cells.

[Eamo]

Hi all

Im not a microsoft office user so I appealing here for some direction (even keywords to google to get me started!) on a problem that I am trying to solve.

Here is the background. Im a linux systems administrator who has been given the job of attempting to automate my employeers invoicing system. Currently its a total mess. It requires 5 days a month of somebody cuting and pasting values from about 30 different internal webpages into ten seperate excel spreadsheets.

I have done the following re written the database querys from the webpages to execute as a batch job and write the results into .csv files. Now (this is where you microsoft office guru's come in!). I want to write a macro to parse the .csv files and automatically import the .csv's into the excel spreadsheets (thus removing the need for days of cutting and pasting!). However I dont want just a "dump" I want to be able to specifiy what information goes into specific cells (I presume this is done by using a macro/template type setup??).

I would be very grateful for any guidance you have.

Kind Regards

Eamo

 

[Andrew Poulsom]

What you want is possible in VBA using sequential file access statements. Can you post a sample csv file specifying where the data should go?

 

[Eamo]

ok here is a typical line from one of the .csv files

"53300","AppName","Providername","1234"

There is pretty much no variation except for what cells the info is going in to.

So taking that as a line I would for example want "AppName" to be inserted into file 53300.xls in cell A5.

"Providername" into cell B5 and "1234" into C5.

Thanks

Eamo

 

[Andrew Poulsom]

Here is an example:

Sub Test()
    Dim FileName As String
    Dim FileNum As Integer
    Dim Data As String
    Dim Tmp
    Dim WB As Workbook
    Dim x As Integer
    Application.ScreenUpdating = False
'   *** Change name of csv file to suit ***
    FileName = ThisWorkbook.Path & "\TestFile.csv"
    FileNum = FreeFile
    Open FileName For Input As #FileNum
    While Not EOF(FileNum)
        Line Input #FileNum, Data
        Tmp = Split(Replace(Data, Chr(34), ""), ",")
        Set WB = Workbooks.Open(ThisWorkbook.Path & "\" & Tmp(0) & ".xls")
        For x = 1 To UBound(Tmp)
            WB.Worksheets(1).Cells(5, x) = Tmp(x)
        Next x
    Wend
    WB.Close SaveChanges:=True
    Close #FileNum
    Application.ScreenUpdating = True
End Sub

The code assumes that the csv file and the target workbook are in the same folder as workbook containing it.

 

[Eamo]

Hi I attempted to run the code you so kindly provided however its giving me a run time error of 1004 the file could not be found.

It shows that its searching for a .xls file rather than .csv I attempted to modify the file to point to a .csv extension but since I have never seen any VB code before I wasnt able to get it to work.. any suggestions.

lets say that the csv file i want to use is 53300.csv

Thanks