I have created the below listed Macro. My goal is to OPEN a workbook named
GraphSendCom.xls and run the below listed Macro to pull another excel file
named "sendcom" into GraphSendcom.xls. This new workbook book has been set
up to pull the daily data into a graph and some overview answers in a more
narrow view. When it runs I can see the "sendcom" report where I want it
then a POP UP comes up and states file not found??? When I hit cancel it
seperates all the columns and does not allow me to use the data how I desire
it.

CAN YOU HELP ME SOLVE MY PROBLEM.




Sub main()

Dim destCell As Range


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

You need to open the workbook before using it with the full pathname. After
the file is open you can reference the workbook with just the filename (no
path)

from:
Dim destCell As Range
With Workbooks("GraphSendCom.xls").Worksheets("sendcom" )

to
Dim destCell As Range
Workbooks.open filename:="c:\temp\GraphSendCom.xls"

With Workbooks("GraphSendCom.xls").Worksheets("sendcom" )

"Krefty" wrote:

I have created the below listed Macro. My goal is to OPEN a workbook named
GraphSendCom.xls and run the below listed Macro to pull another excel file
named "sendcom" into GraphSendcom.xls. This new workbook book has been set
up to pull the daily data into a graph and some overview answers in a more
narrow view. When it runs I can see the "sendcom" report where I want it
then a POP UP comes up and states file not found??? When I hit cancel it
seperates all the columns and does not allow me to use the data how I desire
it.

CAN YOU HELP ME SOLVE MY PROBLEM.




Sub main()

Dim destCell As Range


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