Runtime error 1004,

[pepito_1ton]

HeY all,
Heres the deal, getting a error message when opening a workbook "Runtime error 1004" I dont know much about excel and the user explained it to me as best she could . Here is where the script bugs:

Sub auto_open()
fdist = ActiveWorkbook.Name
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Worksheets("f_cumul").Cells.ClearContents
Worksheets("f_cumul").Cells.ClearFormats
Worksheets("vtes_dj").Select
Range("a1").Select
Selection.End(xlDown).Select
lf = ActiveCell.Row
Range(Cells(2, 1), Cells(lf, 7)).ClearContents
Range(Cells(2, 1), Cells(2, 7)).Value = "x"
Workbooks.Open Filename:="\\LIVE\VOL11\DB_DIST\FOURNIER\CO2_INFO.XLS", ReadOnly _
:=True
Range("A2").Select
ActiveWindow.Visible = False
Workbooks.Open Filename:="\\LIVE\VOL6\Dept\FOURNIER\F_CUMUL.XLS", _
ReadOnly:=True
Sheets("sheet1").Select
Columns("a:g").Copy
Windows(fdist).Activate
Worksheets("f_cumul").Cells(1, 1).PasteSpecial Paste:=xlValues
Workbooks("f_cumul.xls").Close
Windows(fdist).Activate
Worksheets("f_cumul").Columns("D:D").NumberFormat = "d-mmm-yy"
Worksheets("reserve").Select
Columns("e:g").ClearContents
Selection.QueryTable.Refresh BackgroundQuery:=False
Workbooks.Open Filename:="\\live\VOL11\DB_DIST\FOURNIER\RAP_FOU.XLS", _
UpdateLinks:=0, ReadOnly:=True
Sheets("rap_fait").Select
Columns("A:K").Select
Selection.Copy
Windows(fdist).Activate
Sheets("rap_encours").Select
Range("a1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Columns("I:I").Select
Application.CutCopyMode = False
Selection.NumberFormat = "dd-mmm-yy"
Windows("rap_fou.xls").Close
Windows("fou_dist.xls").Activate

Sheets("dist").Select
Range("s5").Select
'Workbooks(fdist).Save

End Sub

Thanks for any help

EDIT: bolded text is where the macro fails.
Thanks

[Leith Ross]

Hello Pepito_1ton,

I don't see where you tell VBA to connect to your SQL server. The Select.Query statement won't work without an established connection to the Database.

Sincerely,
Leith Ross

[pepito_1ton]

I forgot to say that this file works on another computer with very similar configuration. I've dupliacted the odbc sources from the machine that works to the one that doesnt and it didnt help. Does this give you any ideas?

[pepito_1ton]

No ideas of why it works on another machine??
By loking at this script, are there any odbc driver sthat should be in place for it to work , that would at least tell me where to look next.
Thanks again

[Leith Ross]

Hello Pepito_1ton,

It looks like it might be a library reference isn't set in VBA. What database are you migrating the data from? This will help me answer which reference you'll need to check for and add if needed.

Sincerely,
Leith Ross

[pepito_1ton]

Hello Leith Thank you for your reply,
It is a access database. We ahve been getting this problem since Microsoft Office was reinstalled on this computer but in a diffrent language...I tried it on a diffrent computer with office of the same language as on the pc that works and i get "Runtime error 1004, \\LIVE\VOL11\CO2_INFO.XLS could not be found. Check the spelling of the file name and verfiy that the file location is correct.

??The file location is 100% correct and I opened the file myself and it opens fine.
The file CO2_info.xls is also in the macro posted above..hope this information can help you.

[Leith Ross]

Hello Pepito,

Open Excel on the computer your macro runs on. Open the VBE by pressing ALT+F11. Select Tools > References (Alt+T then R). Make a list of all the marked checkboxes listed. Then open Excel on the other computer and check the references it has checked. Check any that aren't on your original list.

Sincerely,
Leith Ross

[pepito_1ton]

I compared the two and they both have the same references checked.

Regards

[Leith Ross]

Hello Pepito,

This looks to be a shared network file. If so, the user may not have the proper network settings and/or access rights to retrieve a shared network file. You may want to have someone in your IT dept look at, or talk to the system administrator about the proper settings.

Sincerely,
Leith Ross

[pepito_1ton]

Ive eleminated that possibility because I am using the administrator account which has access to all the files on the network.