Excel file open and read/write to it

[Rob Tackley]

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

Hi all,

How can I check if I have an excel speadsheet already open? and if it is, how do I read write from it straight away?

All I have managed to do is ensure that the excel spreadsheet is closed then open it again with my vb app.

Please help

[Dököll]

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

Hi all,

How can I check if I have an excel speadsheet already open? and if it is, how do I read write from it straight away?

All I have managed to do is ensure that the excel spreadsheet is closed then open it again with my vb app.

Please help

Hello, Rob!

Found this bit of code interesting and close to what you need, se what it does for you. I did not write this, nor have I tried it:

Option Explicit 

Sub AutomateExcelFromWord()
Dim MSG, Style, Response
Dim Filename As String
Dim SuggestedName As String
Dim oExcel As Excel.Application
Set oExcel = New Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oRng1 As Excel.Range
Dim oRng2 As Excel.Range

Set oWB = oExcel.Workbooks.Add
Set oWS = oWB.Worksheets("Sheet1")

Set oRng1 = oWS.Range("A1")
Set oRng2 = oWS.Range("B1")
oExcel.Visible = False

SuggestedName = "Hello"
Filename = oExcel.GetSaveAsFilename("C:\" & SuggestedName & ".xls", _
"WorkBook (*.xls), *.xls", , "Select or enter a File Name:")
If Filename = "False" Then Exit Sub

'These next few lines inform you if the file already exists.
If (Len(Dir$(Filename)) > 0) Then
           Style = vbYesNo + vbExclamation
           MSG = "The file already exists," & vbCrLf & _
           "would you like to overwrite it?"
           Response = MsgBox(MSG, Style)
    If Response = vbNo Then
           GoTo Cleanup
    End If
End If

'Below is the bit that determines if the file is open.
On Error GoTo ErrorHandler
    Open Filename For Binary Access _
           Read Write Lock Read Write As #1
    Close #1
On Error GoTo 0
    
oRng1.Value = "Hello"
oRng2.Value = "GoodBye"

oExcel.DisplayAlerts = False 'overwrite existing file without prompt
oWB.SaveAs Filename

Cleanup:
    Call oWB.Close(SaveChanges:=False)
    oExcel.DisplayAlerts = True
    oExcel.Quit
    Set oWB = Nothing
    Set oWS = Nothing
    Set oExcel = Nothing

Exit Sub
ErrorHandler:
           MsgBox "E R R O R - The file that your are trying to access," _
           & vbCrLf & "is already open." & vbCrLf & vbCrLf & _
           "Please close the file and try again", vbCritical
GoTo Cleanup
End Sub

Let me know if it does not work, should have it removed from here. Cannot run anything on here as of now...

[Rob Tackley]

Hello, Rob!

Found this bit of code interesting and close to what you need, se what it does for you. I did not write this, nor have I tried it:

Option Explicit 

Sub AutomateExcelFromWord()
Dim MSG, Style, Response
Dim Filename As String
Dim SuggestedName As String
Dim oExcel As Excel.Application
Set oExcel = New Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oRng1 As Excel.Range
Dim oRng2 As Excel.Range

Set oWB = oExcel.Workbooks.Add
Set oWS = oWB.Worksheets("Sheet1")

Set oRng1 = oWS.Range("A1")
Set oRng2 = oWS.Range("B1")
oExcel.Visible = False

SuggestedName = "Hello"
Filename = oExcel.GetSaveAsFilename("C:\" & SuggestedName & ".xls", _
"WorkBook (*.xls), *.xls", , "Select or enter a File Name:")
If Filename = "False" Then Exit Sub

'These next few lines inform you if the file already exists.
If (Len(Dir$(Filename)) > 0) Then
           Style = vbYesNo + vbExclamation
           MSG = "The file already exists," & vbCrLf & _
           "would you like to overwrite it?"
           Response = MsgBox(MSG, Style)
    If Response = vbNo Then
           GoTo Cleanup
    End If
End If

'Below is the bit that determines if the file is open.
On Error GoTo ErrorHandler
    Open Filename For Binary Access _
           Read Write Lock Read Write As #1
    Close #1
On Error GoTo 0
    
oRng1.Value = "Hello"
oRng2.Value = "GoodBye"

oExcel.DisplayAlerts = False 'overwrite existing file without prompt
oWB.SaveAs Filename

Cleanup:
    Call oWB.Close(SaveChanges:=False)
    oExcel.DisplayAlerts = True
    oExcel.Quit
    Set oWB = Nothing
    Set oWS = Nothing
    Set oExcel = Nothing

Exit Sub
ErrorHandler:
           MsgBox "E R R O R - The file that your are trying to access," _
           & vbCrLf & "is already open." & vbCrLf & vbCrLf & _
           "Please close the file and try again", vbCritical
GoTo Cleanup
End Sub

Let me know if it does not work, should have it removed from here. Cannot run anything on here as of now...

Thanks for the code listing, it's not exactly what I needed, however, very useful and it pointed me in the right direction.

Again Thanks

[jebtrillion]

Thanks for the code listing, it's not exactly what I needed, however, very useful and it pointed me in the right direction.

Again Thanks

If thats does not help try this

http://www.gemboxsoftware.com/GBSpreadsheet.htm?gclid=COT484mpqowCFSV0hgodM1UBKQ