Macro for CSV import to specified cells.

Eamo

New Member
Joined
Jul 20, 2007
Messages
4
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jul 20, 2007
Messages
4
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Here is an example:

Code:
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

New Member
Joined
Jul 20, 2007
Messages
4
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
 

Forum statistics

Threads
1,181,723
Messages
5,931,679
Members
436,798
Latest member
spprtpplcm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top