Mass rename and import

  • Thread starter Thread starter Guest
  • Start date Start date
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.
 
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
 
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.
 
Have you tried single-stepping through the code to ensure it's actually
running?
 
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.
 
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?
 
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
Click to expand...
[quoted text clipped - 47 lines]
Click to expand...
 
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
Click to expand...
[quoted text clipped - 47 lines]
for
this. thanks in advanced.
Click to expand...
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top