Problems with accessing a shared file in VBA

Question

0.00/5 (No votes)

See more:

Hello All!

I am trying to write an application simply to edit and reference information from an Excel chart on a shared drive at my workplace. I tested my code on attempting to reference the chart from my C:\ drive and it worked perfectly fine, however when I place it on the shared drive, VB tells me that the file path does not exist. Do I need to add some sort of permission or intent in order to access this drive? My application simply references an excel chart. I can show code if necessary, I have a feeling that this is a simple issue that Im not understanding/...

VB

refSheet = "C:\newtestBook" '' Location of the excel file

Dim completed As Boolean = False
Dim testSheet As New Excel.Application
testSheet.Workbooks.Open(refSheet)
testSheet.Sheets("sheet1").activate()
testSheet.Range("A1").Activate()

When I alter this to be on the shared drive. it tells me that the file path is invalid, it is simply S:\newtestBook and it's not working...

Any suggestions?

Posted 20-Jun-12 8:38am

MrEng

Updated 20-Jun-12 9:42am

Maciej Los

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-06-20T10:46:00

Yes, check the permissions to the shared folder. You need to be able to read/write into it. If the shared folder is inaccessible (not connected), you can't read the excel file.

VB.NET, VB or VBA?

VB.NET

VB

Dim sFileName = "S:\newtestBook.xls"
Dim exApp As Excel.Application = Nothing
Dim exBook As Excel.Workbook = Nothing
Dim exSheet as Excel.Sheet = Nothing
dim exRange = Excel.Range = Nothing

Try
    exApp = New Excel.Application
    exBook = exApp.Workbooks.Open(sFileName)
    exSheet = exBook.Worksheets("Sheet1")
    exRange = exSheet.Range("A1")

    exRange = "Welcome"
    Msgbox (exSheet.Range("A1").Text)
Catch ex As Exception
    MsgBox (ex.Message, Exclamation, "Error...")
Finally
    'to do: free the memory reserved for variables
End Try

VBA/VB

VB

'need reference to the MS Excel...
Dim sFileName = "S:\newtestBook.xls"
Dim exApp As Excel.Application
Dim exBook As Excel.Workbook
Dim exSheet as Excel.Sheet
dim exRange = Excel.Range

On Error Goto Err_Handler
    Set exApp = New Excel.Application 
    Set exBook = exApp.Workbooks.Open(sFileName)
    Set exSheet = exBook.Worksheets("Sheet1")
    Set exRange = exSheet.Range("A1")

    exRange = "Welcome"
    Msgbox (exSheet.Range("A1").Text)

Exit_Handler:
    On Error Resume Next
    'to do: free the memory reserved for variables
    Exit sub

Err_Handler:
    MsgBox (Err.Description, vbExclamation, Err.Number)
    Resume Exit_Handler
End Try