0

I'm creating a code to import a list of csv's into various tables. The code loops through the csv's and tables to import each one a create a table. The code works perfectly but only when I have the csv open. Why do I have to have the csv open? And how do I fix the code so that I do not have to have each csv open? Code is:

Sub ImportData()

Dim filepath(3) As String
Dim tablename(3) As String

filepath(1) = "N:\CompanyA\Model\2018-09-01\Inputs\Tomato.csv"
filepath(2) = "N:\CompanyA\Model\2018-09-01\Inputs\Apple.csv"
filepath(3) = "N:\CompanyA\Model\2018-09-01\Inputs\Pear.csv"

tablename(1) = "8594_Tomato"
tablename(2) = "15692_Apple"
tablename(3) = "10567_Pear"

For i = 1 To 3
    DoCmd.TransferSpreadsheet acImport, , tablename(i), filepath(i), True
Next i

End Sub

Thanks :)

Katie
  • 3
  • 1
  • When you say "have the file open", do you mean you open it in excel or something similar ? Access will have to "open" the file to read the contents, and normally will expect exclusive access to it during the import. But it should then close it again afterwards. – Minty Oct 26 '18 at 13:20
  • Yes I have to open the csv in excel for the code to work. Thanks – Katie Oct 26 '18 at 13:28
  • 3
    Try using the `TransferText` method instead of `TransferSpreadsheet`. See [this answer](https://stackoverflow.com/a/11275468/4717755) for an example. – PeterT Oct 26 '18 at 13:31
  • You never actually open the file in your code. Do it like this: http://www.homeandlearn.org/open_a_text_file_in_vba.html –  Oct 26 '18 at 13:40

0 Answers0