Importing multiple Files

[caj]

I know this is probably very simple but I just can't figue it out... The
code is below. I need to import different files types (.doc, .pdf. xls) into
a table. The files are all in the same folder. I set up a button and it
access the specified folder and enter the link for each file into a record. I
have researched this and found many variations, but i still need a little
Help. The error get from this is that the expression entered is the wrong
data type for the argument.

Private Sub cmdGetFiles_Click()

Dim strFile As String
Dim strFolder As String

strFolder = "C:\Post\Staging\"
strFile = Dir(strFolder & "\*.*")
Do While Len(strFile) > 0

DoCmd.TransferText acImport, "DocMgr", strFolder & strFile, DocFiles
strFile = Dir$()
Loop

End Sub


Thanks

 

[Klatuu]

You have an extra \ when you combine these two:
strFolder = "C:\Post\Staging\"
strFile = Dir(strFolder & "\*.*")

It will result in
C:\Post\Staging\\*.*

I would take it out of strFile

What is DocFiles? It should be a Boolean value of either True or False, but
you really don't need it if the table already exists. If the table doesn't
exist and the Transfer is createing the table, it should be True.

 

[caj]

OK. I took out the extra \. DocFiles is the unbound field to populate. And
now it is asking for and Table name. So I added it and still get a error.

 

[Klatuu]

Did you take the \ out of strFile or strFolder?
The Dir will return just the file name, so you need the \ at the end of
strFolder for your code to work.

I think you need to go reread VBA Help topic on TransferText. It doesn't
work like that. This sentence:
DocFiles is the unbound field to populate.
makes no sense.

The HasFieldNames argument only tells the action whether the data being
transfered has field names or not.

 

[caj]

I took the \ of the strFolder. I may have went about this incorrectly.
I am trying to enter a hyperlink to each file in the folder (C:\post) into
seperate records in a table. Is this the best way to go about doing this?
I hope this makes more sense.

I

 

[Klatuu]

Oh, that is a totally different animal. As written, you are trying to import
data from the files in the folder into a table.
Here is what you need (more or less barring any typos and syntax errors from
writing code in this edit window). It reads throught the folder getting each
file name, the adds a record the table DocMgr with the value of the full path
and name of each file in the field DocFiles.

Dim strSQL As String
Dim strFileName As String
Const conFolder As String = "C:\Post\Staging\"

strFile = Dir(strFolder & "*.*")

Do While Len(strFile) > 0
strSQL = "INSERT INTO DocMgr ( DocFiles ) " & _
"SELECT " & conFolder & strFile & ";"
CurrentDb.Execute strSQL, dbFailOnError
strFile = Dir$()
Loop

 

[caj]

Ok. I think there was a typo and I added

dbFailonError=True.

Now I get an Error when I click the button "Name argument not found".

 

[Klatuu]

That is not correct. dbFailOnError is a constant that tells the Excecute
statement to throw an Access error if the SQL command fails. The Execute
doesn't normally go through Access.

I think I see what the problem is.
Change this line
strSQL = "INSERT INTO DocMgr ( DocFiles ) " & _
"SELECT " & conFolder & strFile & ";"

To
strSQL = "INSERT INTO DocMgr ( DocFiles ) " & _
"SELECT """ & conFolder & strFile & """;"

 

[caj]

OK. I am going to have to play with it some. It's not giving me error but
it's not working. It must be a programmer error. I really, really
appreciate all your help.

 

[caj]

OK. So I am not the quickest programmer with Access and VBA.

It did work!!!! I just had to refresh the form.
Thank you!!! Thank You!!! Thank You!!! I'd kiss you if i could.

 

[ACarella]

Hi Dave or Anyone out there: I was wondering if you could help me.
I have 3 sets of workbooks in 47 different languages.
Workbook A has the following:
A header row that displays the following
ColA = Country
ColB = Language
ColC = Section
ColD = Phrase
ColE = Num
ColF = New
ColG = Country Specific
Then several rows following the heading row

Workbook B has the following:
2 header rows that will not be coming over
ColA = TextCode
ColB = Original Text
ColC = Translation
and rows of data following

Workbook C has the following:
one header row that will not be coming over
ColA = Text Code
ColB = Original Text
ColC = Translation

What I need:
1 Master workbook for each language (meaning, I need a master workbook/sheet
for a language to start with appending workbook a and its headings and append
workbook b and then append workbood c.

with workbook b and c, Col A would append to ColC of Master and Col c would
append to ColD of Master.

I have 47 languages with three spreadsheets each to do from now until Monday.
Is this doable. Of course I will be working on it all weekend.

Can you help me?