--------------------------------------------------------------------------------
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
3 9915
--------------------------------------------------------------------------------
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...
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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Greener |
last post by:
In addition to the previous questions, I wonder, if opening an Excel
file in NON-read-only mode, would the macros on the worksheets work as
well?
Thanks millions!
Helena
|
by: Phoenix |
last post by:
Here's a challenge that is killing me:
I've got 2 web servers and a SQL Server and about 5,000 'users' who
stay connected to the site all day. I have a page that is supposed to
be 'real-time',...
|
by: HAS |
last post by:
Intermittently I am experiencing a problem where an excel file I open is
opened read only because it claims to be locked. I have gone into task
manager and killed all running Excel, I can open the...
|
by: Server Control |
last post by:
Hi,
I am exporting my datagrid to Excel using the standard Export to Excel
Method using :
dg.RenderControl(). and
Page.Response.AddHeader("Content-Disposition", "attachment;filename=" +...
|
by: barnetod |
last post by:
I am trying to open a text file designated by the user.
Then I want to change all lower case values to capital letters.
Then write file.
I am stuck and can not change the characters or am...
|
by: momukhtar |
last post by:
I am using Win32::OLE to write a perl script which opens an excel file. That excel file is password protected and everytime i run that script dialog box pops up and I have to click on Read-Only then...
|
by: Xx r3negade |
last post by:
Sorry for the extreme newbie question, but I honestly can't find this anywhere else. When I do
f = open('/var/www/some_file', 'r+')
it is supposed to open the file for reading and writing,...
|
by: Mel |
last post by:
How do I open an Excel file as a read only document? It would be okay
if it opened in the browser window but I don't want the user to save
any changes and stomp over my original file on the...
|
by: Brian Pinto |
last post by:
Im facing a problem with file stream read/write for a usb device. The code works fine on XP (32 bit). but fails to work on Windows 7 (64 bit).
I'm successfully past problem of getting the device to...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |