Mass rename and import

G

Guest

Need help.
I've got a folder "C:\Documents and Settings\SDF" with aprox 3000 .sdf
files, that I need access to rename to .txt and import automaticaly to table
"SDF" with import specs "importspec" could someone post some VB code for
this. thanks in advanced.
 
D

Douglas J. Steele

Untested air-code. Note that I haven't bothered putting in error handling if
the .txt file already exists in the folder.


Dim strFile As String
Dim strNewName As String
Dim strPath As String

strPath = "C:\Documents and Settings\SDF\"
strFile = Dir$(strPath & "*.sdf")
Do While Len(strFile) > 0
strNewName = Left(strFile, Len(strFile) - 4) & ".txt"
Name strPath & strFile As strPath & strNewName
DoCmd.TransferText acImportFixed, "importspec", "SDF", _
strPath & strNewName
strFile = Dir$()
Loop
 
G

Guest

Douglas thanks a lot, but it doesn't seem to do nothing, I've put this code
behind a module and behind a command button, plain and symple it doesn't do
anything. No error message nothing. Could you please help? Thanks.
 
D

Douglas J. Steele

Have you tried single-stepping through the code to ensure it's actually
running?
 
D

Douglas J. Steele

Put a breakpoint in the code by clicking in the left-hand margin beside the
line of code

strFile = Dir$(strPath & "*.sdf")

When the code executes, it'll stop at that line (with the line highlighted
in yellow). Single-step through using the F8 key.

BTW, I'll be out of town until next weekend, so it's possible I won't see
followup posts to respond to until I'm back.
 
G

Guest

Dim strFile As String
Dim strNewName As String
Dim strPath As String

strPath = "C:\Documents and Settings\SDF\"
strFile = Dir$(strPath & "*.sdf")
Do While Len(strFile) > 0
strNewName = Left(strFile, Len(strFile) - 4) & ".txt"
Name strPath & strFile As strPath & strNewName
DoCmd.TransferText acImportFixed, "importspec", "SDF", _
strPath & strNewName
strFile = Dir$()
Loop

It highlights the until the Do While, and then jumps to the last line the
Loop. Is this normal?
 
M

Michael_Colby

it is saying that the length of your path & file are not greater than zero.
go back and make sure that your sdf files are stored at C:\documents and
settings\sdf\ because that
is where the function is looking. If this is not where your files are then
change the strPath variable according to where your files are.

HTH,
Colby
Dim strFile As String
Dim strNewName As String
Dim strPath As String

strPath = "C:\Documents and Settings\SDF\"
strFile = Dir$(strPath & "*.sdf")
Do While Len(strFile) > 0
strNewName = Left(strFile, Len(strFile) - 4) & ".txt"
Name strPath & strFile As strPath & strNewName
DoCmd.TransferText acImportFixed, "importspec", "SDF", _
strPath & strNewName
strFile = Dir$()
Loop

It highlights the until the Do While, and then jumps to the last line the
Loop. Is this normal?
Put a breakpoint in the code by clicking in the left-hand margin beside the
line of code
[quoted text clipped - 47 lines]
 
D

Douglas J. Steele

Actually, it's just complaining that the length of the file name isn't
greater than 0 (using the Dir function strips off the path), but yes, it
means it didn't find any files with extension sdf in that folder.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Michael_Colby said:
it is saying that the length of your path & file are not greater than
zero.
go back and make sure that your sdf files are stored at C:\documents and
settings\sdf\ because that
is where the function is looking. If this is not where your files are
then
change the strPath variable according to where your files are.

HTH,
Colby
Dim strFile As String
Dim strNewName As String
Dim strPath As String

strPath = "C:\Documents and Settings\SDF\"
strFile = Dir$(strPath & "*.sdf")
Do While Len(strFile) > 0
strNewName = Left(strFile, Len(strFile) - 4) & ".txt"
Name strPath & strFile As strPath & strNewName
DoCmd.TransferText acImportFixed, "importspec", "SDF", _
strPath & strNewName
strFile = Dir$()
Loop

It highlights the until the Do While, and then jumps to the last line the
Loop. Is this normal?
Put a breakpoint in the code by clicking in the left-hand margin beside
the
line of code
[quoted text clipped - 47 lines]
for
this. thanks in advanced.
 
G

Guest

Douglas forget it, the code works fine. My mistake :( I didn't had the
coursor at the begining of the code when I started it, so he wouldn't read
the declaration of the strings.
I've no words to thank you. You took a lot of work of my back. Many thanks
for the straight forward anwser.

Douglas J. Steele said:
Actually, it's just complaining that the length of the file name isn't
greater than 0 (using the Dir function strips off the path), but yes, it
means it didn't find any files with extension sdf in that folder.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Michael_Colby said:
it is saying that the length of your path & file are not greater than
zero.
go back and make sure that your sdf files are stored at C:\documents and
settings\sdf\ because that
is where the function is looking. If this is not where your files are
then
change the strPath variable according to where your files are.

HTH,
Colby
Dim strFile As String
Dim strNewName As String
Dim strPath As String

strPath = "C:\Documents and Settings\SDF\"
strFile = Dir$(strPath & "*.sdf")
Do While Len(strFile) > 0
strNewName = Left(strFile, Len(strFile) - 4) & ".txt"
Name strPath & strFile As strPath & strNewName
DoCmd.TransferText acImportFixed, "importspec", "SDF", _
strPath & strNewName
strFile = Dir$()
Loop

It highlights the until the Do While, and then jumps to the last line the
Loop. Is this normal?

Put a breakpoint in the code by clicking in the left-hand margin beside
the
line of code
[quoted text clipped - 47 lines]
for
this. thanks in advanced.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top