quba2001

I have 2003 xls file which is saved on a web share point. File combines vbs functionalities, of which one is uploading data to this file (from another file on the same share point) by clicking command button.



When I open file from my location it all functionalities are working without problems.



My customer is running it from his location, he is also using Excel 2003, the moment he wants to upload the data he gets the bug.

Message: Method "Worksheets" of object "_Global" failed



I checked and it seems that in the references he has 10.0 object library and I have 11.0



What i can do to unify the coding so it is indifferent from Object Library version?



Pls see coding below (in bold where it is currently buging)

I have read about late va early binding, I guess i'm using late binding
I also check that i can adjust late binding buy standard codes.

Dim oExcel As Object
Dim oWorkbook As Object
Dim oWorksheet As Object
Set oExcel = CreateObject("Excel.Application")
Set oWorkbook = oExcel.Workbooks.Add
Set oWorksheet = oExcel.Worksheets.Add

I do not know where I have to put this coding
Ok Dim will come on top of my coding, before any sub is open
and Set code ???

Should I apply it to the overall workbook?


--------------------------------------------------------------------------------

Private Sub CommandButton1_Click() 'AP_AR upload

' data upload from Oracle AP_AR_TOTALS file


r = 7
r1 = 5

currWorkbook = ThisWorkbook.Name
sFolder = Worksheets("Welcome").TextBox3.Value

fName = "AP_AR_TOTALS.xls" 'name of AP_AR total file
sorceLocation = "http://infoshare.common.eu.corp.toyota.com/VAT/" & "TGB" & "/CURRENTMONTH/Shared%20Documents/" & "AP_AR_TOTALS.xls"


If CommandButton3 = False Then

Application.Workbooks.Open Filename:=sorceLocation 'AP_AR total file activation

Do While Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(r, 7) <> "END" 'adjust tab name !!!!

Workbooks(currWorkbook).Worksheets("Tax Master Tab").Cells(r1, 1) = Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(r, 1)
Workbooks(currWorkbook).Worksheets("Tax Master Tab").Cells(r1, 2) = Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(r, 2)
Workbooks(currWorkbook).Worksheets("Tax Master Tab").Cells(r1, 3) = Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(r, 3)
Workbooks(currWorkbook).Worksheets("Tax Master Tab").Cells(r1, 4) = Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(r, 4)
Workbooks(currWorkbook).Worksheets("Tax Master Tab").Cells(r1, 5) = Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(r, 5)
Workbooks(currWorkbook).Worksheets("Tax Master Tab").Cells(r1, 6) = Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(r, 6)

r1 = r1 + 1
Workbooks(currWorkbook).Worksheets("Tax Master Tab").Rows(r1).EntireRow.Insert
r = r + 1
Loop
Workbooks(currWorkbook).Worksheets("Recon.").Cells(2, 1) = Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(2, 4)
Workbooks(currWorkbook).Worksheets("Recon.").Cells(4, 1) = Workbooks(fName).Worksheets("AP_AR_TOTALS").Cells(3, 4)
End If

Thank you for your suport

DennisW

Hi,
Yes, it's indeed late binding You use. Because Your client has 10.0 and You 11.0 You must use late binding.

As for the code You need to cast the oWorksheet to the Excel.Worksheet Object like: