Trying to create a Macro
 

I need to IMPORT an worksheet named "Report" in a workbook called
SendCom.xls. that is updated each day. The worksheet that I am trying to
update is called "sendcom" in
a workbook called GraphSendCom. I attempted doing this with the following
Macro but it does not work. It keeps coming up with "File Not Found" for
sendcom.xls

Any help would be greatly appreciated.

Sub main()

Dim destCell As Range
Dim totConspWkbk As Workbook

With Workbooks("GraphSendCom.xls").Worksheets("sendcom" )
.UsedRange.Clear
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Workbooks.OpenText Filename:="G:\Gas_Control\EXCEL\DEPT\EOD @ Web
Reports\@ Web SendCom\sendcom.xls", _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), _
Array(30, 1), Array(41, 1), Array(67, 1), Array(78, 1))

Set GraphSendCom = ActiveWorkbook

ActiveSheet.UsedRange.Copy _
Destination:=destCell

GraphSendCom.Close savechanges:=False

Application.Goto destCell, Scroll:=True

ActiveSheet.UsedRange.Columns.AutoFit
Close
End Sub

Trying to create a Macro
 

First, it looks like you're trying to open sendcom.xls as a text file. Is that
sendcom.xls really a plain old text file?

If it is, are you sure that the filename isn't .txt instead of .xls.

And I'd bet that there was a typing mistake in the path if excel can't find your
file.

I'd record another macro when I opened that file manually.

Krefty wrote:

I need to IMPORT an worksheet named "Report" in a workbook called
SendCom.xls. that is updated each day. The worksheet that I am trying to
update is called "sendcom" in
a workbook called GraphSendCom. I attempted doing this with the following
Macro but it does not work. It keeps coming up with "File Not Found" for
sendcom.xls

Any help would be greatly appreciated.

Sub main()

Dim destCell As Range
Dim totConspWkbk As Workbook

With Workbooks("GraphSendCom.xls").Worksheets("sendcom" )
.UsedRange.Clear
Set destCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Workbooks.OpenText Filename:="G:\Gas_Control\EXCEL\DEPT\EOD @ Web
Reports\@ Web SendCom\sendcom.xls", _
Origin:=xlWindows, _
StartRow:=1, DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(8, 1), _
Array(30, 1), Array(41, 1), Array(67, 1), Array(78, 1))

Set GraphSendCom = ActiveWorkbook

ActiveSheet.UsedRange.Copy _
Destination:=destCell

GraphSendCom.Close savechanges:=False

Application.Goto destCell, Scroll:=True

ActiveSheet.UsedRange.Columns.AutoFit
Close
End Sub

--

Dave Peterson