.txt import + saveas + mkdir

[Derek Johansen]

Hey everyone!

I have written a macro to import and manipluate a deliminated text file. It
works well, until I try to have the macro save as! The text files are named
by a program and they have trailing spaces at the end. Example: "EAST 7-621
- G1 .txt" I would like to do a few things that I can't figure out,
and I'm hoping for some assistance:

1. Remove those trailing spaces without removing the inner spaces. Would it
work to Trim(Replace(FileName,".txt",""))?

2. I want the macro to check if a certain directory in this case
"s:/PlanSwift Exports/Jobs/" & strInput is present. If it is, it will create
a new directory inside of that "/WallsPlus" where it will save the file. If
the directory is not present, it will create the strInput directory, then
create the /WallsPlus directory, then save the files within that. *** I
understand how to create the directories if they are not present, but I don't
know how do to it conditionally so that it doesn't overwrite, or give a Path
Not Found error!

3. Save As file. When I try to do a simple ActiveWorkbook.SaveAs I get
errors saying I can't save as that file type. I think it's because it's
currently a .txt file, but i'm not sure how to save a .txt as a .xlsx

I hope these questions are clear, and not asking too much! Hope someone can
give me some help, as I'm getting really frustrated not being able to figure
this seemingly simple stuff out!

 

[Dave Peterson]

#1. Easiest way is to do a little testing to see if it works.
#2. Just create the folder and ignore any errors:

on error resume next
mkdir "s:\PlanSwift Exports\Jobs\" & strInput
mkdir "s:\PlanSwift Exports\Jobs\" & strInput & "\wallsplus"
on error goto 0

This will ignore any error--including invalid folder names and permission
errors.

I'd add a check to see if it was successful:

dim TestStr as string
....
on error resume next
mkdir "s:\PlanSwift Exports\Jobs\" & strInput
mkdir "s:\PlanSwift Exports\Jobs\" & strInput & "\wallsplus"
on error goto 0

teststr = ""
on error resume next
teststr = dir("s:\PlanSwift Exports\Jobs\" & strInput & "\wallsplus" & "\nul")
on error goto 0
if teststr = "" then
msgbox "That directory still doesn't exist!"
exit sub '?????
end if

#3. You have to include the fileformat parm on the .saveas line--and it has to
match the extension.

Ron de Bruin has some notes here:
http://www.rondebruin.nl/saveas.htm
and here:
http://msdn.microsoft.com/en-us/library/bb268022.aspx

You'll see a table that shows this:

FileExtStr = ".xlsb": FileFormatNum = 50
FileExtStr = ".xlsx": FileFormatNum = 51
FileExtStr = ".xlsm": FileFormatNum = 52
FileExtStr = ".xls": FileFormatNum = 56

FileExtStr = ".csv": FileFormatNum = 6
FileExtStr = ".txt": FileFormatNum = -4158
FileExtStr = ".prn": FileFormatNum = 36

 

[Derek Johansen]

I believe the problem with ignoring the errors is that if the directory is
there, it will get overwritten, causing me to lose the data that
waspreviously in the directory. I have three different macros/people writing
to the same directory and the data can't be overwritten

 

[Dave Peterson]

Create a test folder. Put some files in that test folder.

Try the code against that test folder.

What happened to the files in that test folder?