Cannot access 'file.xls'

[DeannaF829]

I'm trying to update an excel spreadsheet with the following code:

Private Sub ExportToFile(objRecord As Recordset, Optional Delimiter As String = ",")

Const xlCSV = 6

Dim EXPORT_FILE_PATH1 As String

Dim objExcel
Dim objWorkbook
Dim objWorksheet

EXPORT_FILE_PATH1 = "C:\Daily_Data.csv"

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\Deanna\My Documents\Daily_Data.xls")

objExcel.DisplayAlerts = False
objExcel.Visible = True

Set objWorksheet = objWorkbook.Worksheets("Sheet1")
objWorksheet.SaveAs EXPORT_FILE_PATH1, xlCSV

Set objWorkbook = objExcel.Workbooks.Close("C:\Documents and Settings\Deanna\My Documents\Daily_Data.xls")

objExcel.quit

End Sub

As soon as it gets to the line that says "Set objWorkbook = objExcel.Workbooks.Open", I get an error Cannot access 'Daily_Data.xls'

Here's the tricky part: This code works fine on one computer, but not on another (same version of VB, Excel, Windows, etc...)

 

[Andrzejek]

Are you trying to open:
Daily_Data.csv
or
Daily_Data.xls
???

Have fun.

---- Andy

 

[DeannaF829]

I'm trying to open the excel file and update it. After it updates, I save it with the same name only as a CSV file.

 

[PatrickIRL]

<<
Here's the tricky part: This code works fine on one computer, but not on another (same version of VB, Excel, Windows, etc...)
>>

Set objWorkbook = objExcel.Workbooks.Open("C:\Documents and Settings\Deanna\My Documents\Daily_Data.xls")

If you look at the path you have it hardcoded to your profile in mydocuments, I doubt the other user has the same path so it won't find the xls file there. So what you need to do is find the MyDocuments folder for the user logged in, do a search for it here, should be plenty of threads.

Patrick

 

[DeannaF829]

I am.
I have the same Set objWorkbook line with both paths. I comment out the one I'm not using and uncomment the one I am using.

Actually, it turns out the 'cannot access' error usually comes up here:
Set objExcel = CreateObject("Excel.Application")

I've also tried Set objWorksheet = GetObject instead thinking maybe if I just got the object instead of opening it, it would work, but it did the same thing. I think it's because I don't actually get past the Set objExcel = Createobject line.

 

[Disferente]

Do you have excel installed on both computers?

 

[berkhof]

Your mistake is here:

Dim objExcel

You need to comment that line out because the CreateObject() function will create and initialize the variable for you.

Here is a snippet of code I used to create a spreadsheet from scratch as a report output. Notice that I never declared the 'xlapp' variable. The code was contained on a form.

=========================================================

Private Sub CreateExcelDocWithData()
'PURPOSE: Performs all functions involved in creating the app, wksheet, and
' posting new data.
' ** doesn't make visible or close the application

Const ReportStartRow As Integer = 4 'allows for headers to be
'inserted by the user
Dim Row As Integer
Dim Col As Integer
Dim RangeStr As String

Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets(1)


With xlSheet


'format the column headers
Row = ReportStartRow
.Rows(Row).Font.Size = 12
.Rows(Row).Font.Bold = True
For Col = 1 To lstFieldsPrint.ListCount
.Cells(Row, Col) = lstFieldsPrint.List(Col - 1)

' With .Cells(Row, Col).Borders(xlEdgeBottom)
' .LineStyle = xlBorderLineStyleContinuous
' .Weight = xlThin
' .ColorIndex = 3
' End With

Next Col


'put data in their cells
RSMain.MoveFirst
For Row = (Row + 1) To (RSMain.RecordCount + Row)

For Col = 1 To lstFieldsPrint.ListCount
If (InStr(RSMain.Fields(lstFieldsPrint.List(Col - 1)).Name, "Phone")) Then
'check to see if the field is a phone number...
'if it is, format with hyphens
'check to see that there is a phone number
If (Not IsNull(RSMain.Fields(lstFieldsPrint.List(Col - 1)))) Then
.Cells(Row, Col) = MySystem.AddHyphensToPhoneNum(RSMain.Fields(lstFieldsPrint.List(Col - 1)))
Else
'leave the cell blank
End If

ElseIf (RSMain.Fields(lstFieldsPrint.List(Col - 1)).Type = dbDate) Then
'check to see if the field is a date
'if it is, ensure that Excel gets it as a date
If (Not IsNull(RSMain.Fields(lstFieldsPrint.List(Col - 1)))) Then
.Cells(Row, Col) = Format(RSMain.Fields(lstFieldsPrint.List(Col - 1)), "DD MMM YYYY")

Else
'leave the cell blank
End If

Else 'treat as a non-phone number field
.Cells(Row, Col) = RSMain.Fields(lstFieldsPrint.List(Col - 1))

End If

Next Col

If (RSMain.EOF = False) Then RSMain.MoveNext

Next Row

'Autosize Columns
For Col = 1 To lstFieldsPrint.ListCount
'Debug.Print """" & Chr(Col + 64) & """"
'RangeStr = """" & Chr(Col + 64) & """"
.Columns(Chr(Col + 64)).AutoFit 'convert from 1 to "A", 2 to "B", etc.
'convert numbers to letters to facilitate
'Excel cell naming convention
'.Cells(Row, Col) = lstFieldsPrint.List(Col - 1)

Next Col

' For Col = 1 To lstFieldsPrint.ListCount
' .Columns.AutoFit
' .Cells(Row, Col) = lstFieldsPrint.List(Col - 1)
'
' Next Col
End With

End Sub

 

[DeannaF829]

I commented out the Dim objExcel and I got a Variable not defined error.

I'm also not creating a spreadsheet from scratch. It already exists which I believe is the problem. Something somewhere has it and won't let it go so I can write to it. I tried sharing the spreadsheet too.

 

[JoeAtWork]

Your mistake is here:

Dim objExcel

I'm sorry but you are mistaken. There's nothing wrong with declaring the variable beforehand (and many of us would argue that is best programming practice).

Deanna, what happens if you open Excel and try to load the file into it (from the exact same path you are trying from your code)?

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[strongm]

>and many of us would argue that is best programming practice

We certainly would. We might even argue that we like strong typing, and thus should also declare what type the variable is going to be wherever possible ...

 

[JoeAtWork]

Yeah, except with the Office applications I often use CreateObject so I don't have to worry whether the user has Office97, 2000, 2003, etc.




Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[DeannaF829]

I've given up on the Excel thing. I'm going to have to learn how to use FileSystemObject and do the update to a CSV file without using Excel at all. There are too many issues with MDAC, DAO, ActiveX, Excel settings, Windows settings, dll files, etc... Thanks for trying though. Have a good weekend.

 

[strongm]

Sure, Joe - but in that case I'd Dim it as Object ...

 

[JoeAtWork]

Strongm - agree 100%. I hate the variant type (it creates variables with amnesia since they can't tell you what they are).

Deanna - if all you want to do is creae a CSV file, Excel was never the good choice in the first place. It's just a simple text file, you probably don't even need the FileSystemObject (unless you need to do things like verifying file paths, etc.). All you need to do is open a file for output, construct your delimited lines one at a time and output them to the file, and close the file. Much simpler, less code, and you don't have to worry about whether Excel is installed on the computer.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[DeannaF829]

i need to update a record in a csv file. I was using Excel so I could update the record using SQL and then saving the updated excel file as a csv