How to trim a filename in a macro

this should do it:


hth
kevin

Thanks - Something still doesn't work. Below is my code.....
I call a routine (getname) that returns "infile". Macro aborts on the "input_wks" subroutine at the line indicated (where it looks to the sheet).

doug.

Call getname(infile)
If infile = False Then
Exit Sub
Else
insheet = Left(infile, Len(infile) - 4)
Call input_wks(destfilename, "lgd1000", destrange, infile, insheet, "a2")
End If

Sub input_wks(destbook, destsheet, destrange, sourcefile, sourcesheet, sourcerange)
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet
Dim ws2 As Worksheet, rng1 As Range, rng2 As Range
Set wb1 = Workbooks(destbook)
Set ws1 = wb1.Worksheets(destsheet)
Set rng1 = ws1.Range(destrange)
rng1.Value = " "
Workbooks.Open FileName:=sourcefile
Set wb2 = ActiveWorkbook
Set ws2 = wb2.Worksheets(sourcesheet) ABORTS ON THIS STATEMT
Set rng2 = ws2.Range(sourcerange)
Set rng2 = ws2.UsedRange
Set rng1 = rng1.Resize(rng2.Rows.Count, rng2.Columns.Count)
rng1.Value = rng2.Value
wb2.Close False
End Sub

what is your variable sourcesheet? and are you declaring this variable? i'll check back on this tomorrow, am logging off for the day

kevin

Below is a bit more of the code. I set the variable "insheet" as a sheet, I then pass it as a parameter to the subroutine. In the subroutine, the range is also set at a sheet.

Sub inputonly()
Dim insheet As Worksheet
destfilename = ActiveWorkbook.Name
Range("a2").Select
destrange = ActiveCell.Address
Call getname(infile)
If infile = False Then
Exit Sub
Else
insheet = Left(infile, Len(infile) - 4)
Call input_wks(destfilename, "lgd1000", destrange, infile, insheet, "a2")
End If

Sub input_wks(destbook, destsheet, destrange, sourcefile, sourcesheet, sourcerange)
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet
Dim ws2 As Worksheet, rng1 As Range, rng2 As Range
Set wb1 = Workbooks(destbook)
Set ws1 = wb1.Worksheets(destsheet)
Set rng1 = ws1.Range(destrange)
rng1.Value = " "
Workbooks.Open FileName:=sourcefile
Set wb2 = ActiveWorkbook
Set ws2 = wb2.Worksheets(sourcesheet)
Set rng2 = ws2.Range(sourcerange)
Set rng2 = ws2.UsedRange
Set rng1 = rng1.Resize(rng2.Rows.Count, rng2.Columns.Count)
rng1.Value = rng2.Value
wb2.Close False
End Sub

1) When I EXCLUDE the Dim statement (bolded above in the calling program) the macro fails in the subroutine in the "set ws2" statement (bolded). The macro goes so far as to read in the input file (named "infile"), but doesn't go through the copy routine


2)When I INCLUDE the DIM statement the macro gives the error message
"Object variable or with block variable not set". It never even gets to read in the input file.

Thanks,

if possible, it would help if you could post your entire code, then I or someone else could test it as you have written it and see if we can repeat the problem and possibly come up with a solution

thanks,
kevin

OK here it all is. I've deleted some lines that are irrelevant to the problem(like I do the "call input" several times).

Sub inputonly()
Dim insheet As Worksheet
destfilename = ActiveWorkbook.Name
Range("a2").Select
destrange = ActiveCell.Address
Call getname(infile)
If infile = False Then
Exit Sub
Else
insheet = Left(infile, Len(infile) - 4)
Call input_wks(destfilename, "lgd1000", destrange, infile, insheet, "a2")
End If
Windows(destfilename).Activate
Sheets("lgd1000").Select
End Sub

Sub input_wks(destbook, destsheet, destrange, sourcefile, sourcesheet, sourcerange)
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet
Dim ws2 As Worksheet, rng1 As Range, rng2 As Range
Set wb1 = Workbooks(destbook)
Set ws1 = wb1.Worksheets(destsheet)
Set rng1 = ws1.Range(destrange)
rng1.Value = " "
Workbooks.Open FileName:=sourcefile
Set wb2 = ActiveWorkbook
Set ws2 = wb2.Worksheets(sourcesheet)
Set rng2 = ws2.Range(sourcerange)
Set rng2 = ws2.UsedRange
Set rng1 = rng1.Resize(rng2.Rows.Count, rng2.Columns.Count)
rng1.Value = rng2.Value
wb2.Close False
End Sub

Sub getname(newfn)
newfn = Application.GetOpenFilename(filefilter:="Excel Files (*.xls), *.xls", Title:="Please select a file")
If newfn = False Then
' They pressed Cancel
MsgBox "Stopping because you did not select a file"
Exit Sub
Else
'Workbooks.Open FileName:=newfn
End If
End Sub

not sure if this is a problem or not, but i don't see anywhere in your code where you are assigning a value to the variable 'infile', before you try to use it to assign a value to 'insheet'. this may be causing problems, fix this by assigning it the name of the workbook that is opened by your 'getname' sub, then see where that gets you


hth
kevin

Kevin,
Thanks for all your suggestions.

Infile is the output of the "getname" subroutine. I know that it is being set because the "input_wks" macro (when I run it) is opening the file.

Does the getname subroutine actually open the file or just get the NAME of the file to open?

doug

I THINK THAT I FOUND THE PROBLEM!!

The variable "infile" has the full path. So I need to strip out just the filename (without extension) from "infile" - not just delete the last 4 positions.

So here's the problem.

"infile" has the following value:
O:\LGD Database\Extract Reformater\2003-02-obligor.xls
and I need the variable "insheet" to be
2003-02-obligor

doug