The Chdrive command that works on 2003 XP is

ChDrive "C"


You shouldn't be running a macro in another workbook. The better way is to
put a macro in a newworkbook and in this macro have a GETOPENFILENAME command
that opens the workbook with the data. Like this

Private Sub CommandButton1_Click()
fileToOpen = Application _
.GetOpenFilename("Excel Files (*.xls), *.xls*")
If fileToOpen = False Then
MsgBox("Cannot open file - Exiting Macro")
exit Sub
End If

set Databk = workbooks.open(FileName:=filetoOpen)

with DataBk

'put your code here

end with

Databk.close Savechanges:=True

end sub

"Paul H" wrote:


================================================== =======

Joel,

Getting error or ChDrive = "C" - says "Compile error: Argument not optional" in a box.

Also can't figure out how to put my macro into a new workbook so I can distribute this spreadsheet by itself, with a button to cause our macro to run.

I can't get the button to point to the new macro that stands alone in a new workbook.

I'll want to put the output somewhere maybe a different folder, but I doubt it) having the same name, but extension .XLS. My new book helps a lot, but leaves a few details like this or me to figure out.

Thanks,
Paul

================================================== =======


"Joel" wrote in message
...
I often use the macro recorder to get the prper syntax of methods, but I
alwasy modify the recorded maqcro to simplify the code. I also remove the
Section methods which aren't efficient. Switching from worksheets slows
down
the code and make it hard to understand. I you need to format you r columns
use "Numberformat" which is easier to understand. The number 1 in the array
method is General Formating which only insures that any formating on the
worksheet is removed.

"Paul H" wrote:


================================================== =======

Dave,
The network drives are all mapped, and for each application, never change.
Only the quantity of the .CSV files in the folder increases, but their
names
contain date and time stamps, so being able to display them, newest at the
top, is important. For testing they are mapped (usually L: or S:) to
folders off the root of my C: or D:. In production, they are on other
computers.

Joel,
I like how you simplified the code - most of which was created when I
recorded the macro. The skeleton empty .XLS or .XLSX file contains the
sizes and formats of the columns. Does that still mean the "arrays of
1's"
is not needed? So I can probably clean up other files if I want to. But
I
usually don't care about extraneous code if it doesn't show the process
down
or cause a problem.

Thank you Dave, Joel, and Shane.

================================================== =======


"Dave Peterson" wrote in message
...
If that path on the network drive is not mapped (you're using the UNC
Path),
then chdir won't work. But there's a windows API that will work in all
cases:

Option Explicit
Private Declare Function SetCurrentDirectoryA Lib _
"kernel32" (ByVal lpPathName As String) As Long
Sub ChDirNet(szPath As String)
Dim lReturn As Long
lReturn = SetCurrentDirectoryA(szPath)
If lReturn = 0 Then Err.Raise vbObjectError + 1, "Error setting path."
End Sub
Sub Loader1()

Dim myFileName As Variant
Dim myCurFolder As String
Dim myNewFolder As String

myCurFolder = CurDir
myNewFolder = "\\share\folder1\folder2"

On Error Resume Next
ChDirNet myNewFolder
If Err.Number < 0 Then
'what should happen
MsgBox "Please change to your own folder"
Err.Clear
End If
On Error GoTo 0

myFileName = Application.GetOpenFilename(filefilter:="CSV Files,
*.CSV",
_
Title:="Pick a File")

ChDirNet myCurFolder

If myFileName = False Then
MsgBox "Ok, try later" 'user hit cancel
Exit Sub
End If

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & myFileName, Destination:=activesheet.Range("A1"))
.Name = "CreditData-021809dater"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1, 1, _
1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

ActiveSheet.Range("A1:AO1").Font.Bold = True

End Sub


I left all the defaults in the code. You never know when you'll want to
change
them. (I thought some of the fields would be text (but I may be
misremembering).)

ps. Untested, but it did compile