Can you help me solve!
 

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
Was this

Can you help me solve!
 

Krefty,

An idea: you can create a query to the worksheet you want to import, and
write code to update the query on Workbook_Open

"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
Was this