Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




How To Access Excel Workbook Already Opened?


Hi,everyone

How to access excel workbook, already opened?

thanks for your reply.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Acting Over Opened Excel Workbook From VB6
I have an opened workbook with some spreadsheets and my application has to take control of the workbook to modify some cells. I unsuscesfully used the folowing code

Ser ExcelApp = Nothing

Set ExcelApp = Excel.Application

Whit ExcelApp

.Workbooks("myWKB").Activate

' >>>> ERROR HERE

.....

' changes in WB

....

end with
This piece of code oppens a new Excel instance rather that take control of the existing one. As consequence it give me an error when try to activate a workbook that is not opened in the new instance.

Note that I don't like and cannot open the workbook again in the new instance. How can I take control of the existing instance of excel where the workbook was opened?

VBA Open Form When Excel Workbook Opened
Anyone know how to trigger a form/macro to automatically load when you open an Excel sheet?

I hate VBA... but it's all I have ATM.

Prevent WorkBook For Being Opened By Diff. Excel Version
Hello again.
I have a workbook with Macros etc. I made it using Excel Xp and in the environment where I have this workbook we can find Excel 98, 95.

The workbook is not compatible with all Excel applications, especially with 95 or 98.
How I can prevent this workbook being opened by excel 98 or 95?
Or
I was thinking about to put a message when workbook is opened saying that this work just with Excel xp or 2000 but I want something that can check the version of their application and in that case saying "Your application is not compatible"
Any suggestion?
Thanks in advance.
Gonza.

Macro To Save Workbook Is Picking Up Previously Opened Workbook Filename Causing An Error.
Hi,

I have written a macro to save a file in 3 different locations (one being an HTM file as well). I have several schedule files (created by doing File Save As from an original) that this macro will be applied to. I have inserted the macro into each file and created a Macro Menu item which has been mapped to the "This Workbook" macro item that i have written. (End Background, Begin Problem/Question) When I run this macro from one sheet, close that workbook, open a different workbook and run the macro, my macro looks for the previous file name. I am not "quite" sure if the problem is code related or tied to the menu assignment. I have included the code for reference and hope someone may have a better way of doing this.

Code:
Sub SaveHTML()

    Dim conNetPath As String
    Dim conHomePath As String
    Dim conBUPath As String
    Dim fName As String
    Dim HTMLFile As String
    
    conNetPath = "\atlnewsf04Sudhir KeepStuffskeds"
    conBUPath = "c:Schedules"
    conHomePath = ActiveWorkbook.FullName
    fName = ActiveWorkbook.Name
    HTMLFile = Replace(fName, "xls", "htm")

    Application.DisplayAlerts = False

    ActiveWorkbook.SaveAs Filename:= _
        conNetPath & HTMLFile, FileFormat:=xlHtml, _
        ReadOnlyRecommended:=False, CreateBackup:=False

    ActiveWorkbook.SaveAs Filename:= _
        conBUPath & fName, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

'MsgBox conHomePath
    ActiveWorkbook.SaveAs Filename:= _
        conHomePath, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

    Application.DisplayAlerts = True
    
    
    
End Sub



When you call this macro from the first file, it works fine. If you close that file and open another file with the same macro and try to run it, it looks for the previously opened file.

Not sure where I goofed. I have included two of the spreadsheets to possibly demonstrate.

Thanks
Scott Medaugh

How To Check If Workbook Opened Using VBA Could Be Opened Or Not?
Hi All

I am trying to open a workbook using the VBA statement
Code:
Workbooks.Open "c:deepaksourcedataabc.xls"
in a procedure.

How can I record the success or failure of this event (whether the file could be opened or not)? The intent being to inform the user and quit if it couldn't be opened for whatever reason or read data therein and continue with further processing if it could be opened.

In pseudocode language this is what I am looking at doing :-


Code:
If file cannot be opened then
Inform user and exit procedure
Else
Open the file and read data therein and continue further processing
End if


Best regards


Deepak Agarwal

Problem With Excel Data When A Spreadsheet Is Opened In Access
I use the following code to open a spreadsheet that has 24 columns
However I can only get the data from the first two rst(2),rst(3),rst(4) all return null even though thay contain data.
Its as if the driver is only returning the first 2 columns.

Anybody had this sort of problem before?

Dim dbs As Database
    Dim rst As Recordset
    Dim strDoc As String
    
    strDoc = "mySpreadsheet.xls"
    Set dbs = OpenDatabase(strDoc, False, False, "Excel 5.0;HDR=No;")
    
    Do Until rst.EOF
            Debug.Print Trim(rst(0)); " "; Trim(rst(1)); " "; Trim(rst(2)); " "; Trim(rst(3)); " "; Trim(rst(4))
    
        rst.MoveNext
    Loop
    dbs.Close
    Set rst = Nothing

Need Help With Switching To A Workbook Already Opened
HELP! I am trying to write a code that will switch back and forth between 2 workbooks, but need it to be relative. (Meaning one workbook will not always have the same name.)

Creating A New Excel Workbook From MS Access
Can anybody help??

I have an Access database with a command button on a form to export data to Excel. I have written the routine to export the data to the following:

1) Open an Excel application
2) Add a new workbook
3) Create a new worksheet(s)
4) Save the workbook
5) Close the Workbook
6) Quit the Excel application

This all works fine, but the problem I have got is that I'm unable to open the saved workbook after the routine has completed. There's no error message - just doesn't open the workbook. I looked into this and discovered that after the routine has run a process is left running in the background called EXCEL.EXE, and the only way to end this process is to quit the Access database or manually end the process through Task Manager.

What I want to know is why does this process get left running in the background and is there a way to end it via the routine I've got. The complete code for the routine is as follows:Private Sub cmdExportData_Click()

Code:
Dim dbs As Database
Dim rs As Recordset
Dim rst As Recordset
Dim ExpDate As Date
Dim ExpFile As String
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim iRow As Integer
Dim ShtCount As Integer
Dim CostCode As Integer
Dim Flag As Boolean

Set objXL = CreateObject("Excel.Application")
Set objWkb = Workbooks.Add

Flag = True
objXL.Application.DisplayAlerts = False
Do Until objWkb.Worksheets.count = 1
objWkb.Worksheets(1).Delete
Loop
objXL.Application.DisplayAlerts = True

ExpDate = Date
ExpFile = "Liblist" & ExpDate

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tblCodes", dbOpenDynaset)

Do While Not rs.EOF
CostCode = rs!CodeRef

If Flag = True Then
Flag = False
Set objSht = objWkb.Worksheets(1)
Else
Set objSht = Worksheets.Add
End If
objSht.Name = CStr(CostCode)
iRow = 3

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblLibs WHERE LibDesc LIKE '*" & CStr(CostCode) & "*'")
rst.MoveFirst

Do While Not rst.EOF
objSht.Cells(iRow, 1).Value = rst!LibName
objSht.Cells(iRow, 2).Value = rst!LibDesc
objSht.Cells(iRow, 3).Value = rst!LibSize
iRow = iRow + 1
rst.MoveNext
Loop

rst.Close
rs.MoveNext
Loop
rs.Close
Set objSht = Nothing

Set rs = dbs.OpenRecordset("tblExportLog", dbOpenDynaset)

With rs
.AddNew

!ExportFile = ExpFile

.Update
.Close
End With

Set rs = Nothing
Set rst = Nothing
Set dbs = Nothing

objWkb.SaveAs ("D:DatabasesLiblist" & ExpFile)

objWkb.Close
Set objWkb = Nothing

objXL.Application.Quit
Set objXL = Nothing

End Sub

Run A Sub Procedure Each Time A Workbook Is Created/opened!
Hi Everyone

Is there a way to do this :-

Each time a workbook is created (new workbook) or opened (existing workbook), I want a procedure to run which will establish a link to a particular AddIn - it can be safely presumed that the referred to AddIn (reference) is definitely available (existing) - so we don't have to allow for the scenario that the referred to reference cannot be found.

From what I understand of the requirements herein, this obviously cannot be run from within a specified workbook and needs to be run at the application level.

Any suggestions how to go about doing this?


Best regards


Deepak Agarwal

Can Anyone Save My Life?? {corrupt Workbook - Cannot Be Opened}
hi there! i´ve worked months and months in a file and now i cannot open it... can i at least try to save only the vba code? everytime i try to open it it gives me an error msg ... i just wanna save the code...
well i didn´t get to attach the file cause it´s over 2mg
thanks!

Count The Number Of Times A Workbook Is Opened
Guys,
How would one count the number of times a workbook had been open. I have on the Workbook Open event declared a integer variable called count and each time it is opened this value should be incremented by 1, then on the workbook close event I have the code take count and increase it by one so that way what I expect to get when I open the workbook for the thrid time is the value of count = to 3 however it resets the value back to 0 and the count equal one again. So if anyone has any ideas I would love to hear them. Thanks as always for your time and devotion to helping begginers like me grasp VBA for excel.

Access Data Export To Excel Workbook
Hi,

I have created a code that exports the results of an Access query to an Excel sheet. When more queries are entered, they will be placed in the workbook as different sheets.

The problem is that while the code is running, and in the meanwhile I perform some other actions in other Excel workbooks, it sometimes happens a by code created sheet is inserted in another workbook then the one created by the code. I don't really know why and when (which other Excel actions) this happens.

How to solve this?

This is the code, thanks:

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

Option Compare Database

Public Function OpenInExcel(queryName As String, excelName As String, Optional param As String = "", Optional sheetName As String = "Data") As Object
'On Error GoTo errorHandler
Dim xl As Object
Dim wb As Object
Dim ws As Object
Dim rng As Object
Dim rs As Object
Dim cmd As ADODB.Command

Set xl = CreateObject("Excel.Application")
xl.Caption = excelName
xl.Visible = False
Set wb = xl.workbooks.Add

wb.sheets(3).Delete
wb.sheets(2).Delete

addToExcel queryName, xl, param, sheetName
wb.sheets(1).Delete

xl.UserControl = True
xl.Visible = True

Set OpenInExcel = xl

Exit Function

errorhandler:
MsgBox Err.Description, vbCritical, "Fout " & Err.Number
End Function

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

Public Function addToExcel(queryName As String, xl As Object, Optional param As String = "", Optional sheetName As String = "")
Dim wb As Object
Dim ws As Object
Dim rng As Object
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

xl.Visible = False
xl.UserControl = False

If param = "" Then
Set cmd = New Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "SELECT * FROM [" & queryName & "]"
Set rs = cmd.Execute()
Else
Set cmd = New Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandText = "SELECT * FROM [" & queryName & "]"
Set rs = cmd.Execute(Parameters:=Array(param))
End If

If rs.CursorType <> CursorTypeEnum.adOpenForwardOnly Then
rs.MoveLast
If rs.RecordCount > 3000 Then
If MsgBox("Er worden " & rs.RecordCount & " rijen geëxporteerd. Dit kan enkele momenten in beslag nemen." & vbNewLine & vbNewLine & "Wilt u doorgaan?", vbYesNo + vbExclamation, excelName) = vbNo Then
Exit Function
End If
End If
rs.MoveFirst
End If

Set wb = xl.activeworkbook
Set ws = wb.worksheets.Add(after:=wb.activeSheet)
If sheetName <> "" Then ws.name = Left(sheetName, 31)

For t = 0 To rs.Fields.Count - 1
ws.Cells(1, t + 1) = rs.Fields(t).name
Next

rij = 1
While Not rs.EOF
For kolom = 0 To rs.Fields.Count - 1
With rs.Fields(kolom)
If .Type = adBigInt Or .Type = adDecimal Or .Type = adDouble Or .Type = adInteger Or _
.Type = adNumeric Or .Type = adSingle Or .Type = adSmallInt Or _
.Type = adTinyInt Or .Type = adUnsignedBigInt Or .Type = adUnsignedInt Or _
.Type = adUnsignedSmallInt Or .Type = adUnsignedTinyInt Or .Type = adVarNumeric Then

' dan is het een getal
ws.Cells(rij + 1, kolom + 1) = .value
Else
' alles anders dan een getal doen we een apostrophe voor
' dit zorgt ervoor dat alles netjes wordt weergeven
ws.Cells(rij + 1, kolom + 1) = "'" & .value
End If
End With
Next
rs.MoveNext
rij = rij + 1
Wend

ws.Rows(1).AutoFilter
ws.Range(ws.Cells(1, 1), ws.Cells(1, rs.Fields.Count)).Font.Bold = True
ws.Range(ws.Cells(1, 1), ws.Cells(rij, rs.Fields.Count)).Columns.AutoFit
ws.Range(ws.Cells(1, 1), ws.Cells(rij, rs.Fields.Count)).Rows.RowHeight = 12.75

xl.UserControl = True
xl.Visible = True

rs.Close
Exit Function

End Function

Opening An Excel Workbook From A Template In Access VBA
Hi there.....this may be a doosie but i was wondering if anyone knew how to go about opening an excel document derived from an excel template through VBA code in my Access form. I understand whenevr you open a template a new workbook is created but have no idea how to handle the code.

Any insight would be helpful.

Thanks

VBA Run-Time Errors Only When Workbook Is Opened With Internet Explorer
Hello,
My name is Jeff and I have a excel vba question.
My question pertains to a problem that occurs when someone is running
an excel workbook in Internet Explorer. Also, the use has to open this workbook in internet explorer. When the user tries to run a macro several errors arise. First, the a 1004 error in relation to DisplayAlerts.
This was rememdied by commenting out the displayalerts. However, once that problem was 'fixed'. Now the user runs into a 1004 error with the object Sheets causing the error.
My problem is I can't test for this error since my IE won't open excel files directly (i.e. it always has excel itself open the workbook). I've tried everything I know in the File Extensions Options(i.e. making sure the Browse in Same window option is checked for the .xls extension.)
Is there something else I need to be doing? [edit: ignore this first question]

My other question is more general.

I'm starting to think that if someone uses IE to run workbooks, that some of the VBA Code doesn't run the way its supposed to. If this is the case can anyone tell me if this is just for specific commands or is it a widespread problem? [Edit: I understand LadyAmazon asked about this issue before, and I am currently working through the forum to see a solution, Basically my problem pertains to commands that use the sheets("sheet1") etc.]


Thank you for your consideration of this post
Jeff

Create Excel 2003 Workbook From Access 2007
Hi All,
Can't find help on this anywhere at all.
I'm using late binding 'Set oExcel = CreateObject("Excel.Application")' to create an excel workbook, but it needs to be in version 11 (2003) not 12 (2007) for the recipients to view.
Anyone have ideas on how to do this programmatically?
Cheers
Brian

Is It Possible To Run A Macro In An Excel Workbook From An Access Database - Resolved
I am in the process of making a database in Access 97 that has links to a couple of Excel 97 Worksheets (in same Workbook). There is a macro in the workbook that I'd like to run, preferably without getting the user to have to open the workbook, run the macro, then return to Access. I'm almost sure that I read somewhere that this was possible, but I've no idea how it would be done.
Any suggestions would be appreciated.
Thanks in advance.
JackiD
Resolved
with thanks to Suat
See Below
Create a button on your form, then put a command button on this form (click Cancel when it goes to wizard to create button) then right click on the button and point to Built Event. Select Code Builder in the list. It will goto VBA and show you the Click event of the Command button, just like below:
 
Private Sub Command1_Click()

End Sub
 
Now put your code into this event sub, it will seem like below when you place the code in it:
code:
 
Private Sub Command1_Click()
Dim xls As Object
Dim wrk As Object
Set xls = CreateObject("Excel.Application")
Set wrk = xls.Workbooks.Open("D:Excel97 FilesTrial.xls")
xls.Run ("Module1.ReSetTime")
wrk.Close False
xls.Quit
Set xls = Nothing
End Sub
 
(Module1.ReSetTime: means you are calling a macro in workbook, named as "ReSetTime" and which is placed in a module named as "Module1")
I hope this helps.
Suat




Edited by - JackiD on 7/27/2003 9:52:51 AM

Excel Application.workbooks.open(filename) Doesn't Return Excel Workbook
I use the following code to open work book. Then would like to reference the excel work book using excelwrk.

I can't reference excelwrk as excelapp.workbooks.open("c: est.xls") doesn't return any work book and Excelwrk is nothing.
I have no choice, have to reference worbook using excelapp.activework

Is there anything wrong with my code. Please advise. When I run application, there is no error. I am using Excel 8.0 object library.



Dim excelapp as Excel.Application
Dim excelwrk as Excel.Workbook
on error resume next

Set excelapp = GetObject(, "Excel.Application")
if err=429 then
Set excelapp = CreateObject("Excel.Application")
end if

Set excelwrk = excelapp.Workbooks.Open("c: est.xls")

Thanks

Yee

Reading Values From A Closed Excel Workbook (Excel 97) **RESOLVED**
Morning all,
This is further to an old post.

I am looking to read data values from a closed file and write them to an open workbook. I am hoping to do this not simply value by value but using large ranges.

e.g. On sheet1 of the closed file I need to read values for the range("A1:F100"). e.g. if the closed file were open:

Code: Workbooks(OpenFile).Sheets(Sheet1).Range(A1:F100).Value=Workbooks(ClosedFile).Sheets(Sheet1).Range(A1:F100).Value

a) Does anyone know the code to read values from a closed file (even if value by value)?
b) Is it possible to make this method read large data ranges?

Any help would be great.
Cheers,
M_B



Edited by - M_B on 5/28/2004 4:09:44 AM

More Than One Excel Opened At The Same Time
Would anyone know if it is possible to run two excel packages at the same time. I wrote a package in VB excel, and I want to be able to run two packages on two separate excel sessions. At the moment it allows me to open two sessions of the VB package, but when Excel is running one package, it will not allow me to enter the second package until the first has run. I would really want to beable to run both excel sessions at the same time.

Excel File Opened?
How can I check if an Excel file is opened?

My program is only used by 4 people, it uses an Excel file as database (temporary solution till IST dept. will develop something more adequate), they updates statuses, add/delete lines and staff, everything happens in matter of seconds (e.g. open file, add line, save and close file) but still there's a small chance that 2 users will try to update at the same time and I need to make my program wait to open the file till it's closed by the other user.

Any sugestions will be appreciated.

How To Save As The Excel Opened By Asp.net
I already displayed excel data in datagrid.
Kindly help me on how to edit then save as the opened excel data file using asp.net.



Edited by - bamboo on 10/7/2003 9:54:51 PM

Copy Excel Data To New Excel Workbook HELP!
I have a excel workbook with thousands of peoples names and info. A persons record can be in the workbook more than once. I want to create a macro or somthing in excel to copy all of the same people with the last name Smith for example to a new workbook. How do I go about doing this, anyone with example code or help.

Thanks

Non-Termination Of Excel.exe Even After Closing The Excel Workbook Through VB
Hi
I have a VB code that executes every one minute and checks for a time match. If the time matches then it has to execute a code in which i open several workbooks one by one fill in some details and then close the workbook. Since it is a continuously running program i cannot close the form and the excel.exe is not getting terminated in the task manager. As a result Excel is getting hanged and i am unable to open any Excel file by double clicking in the explorer(May open once).

This is my code

Private Sub Timer1_Timer()
If Format(Now, "hh:mm") = "12:00" Then
call_func = open_excel()
End Sub

Public Function open_excel()
Excel.Workbooks.Open "D:Tables emplate.xls"
' My code
Workbooks.Close
End Function

This code should be run and the form should not be closed. I tried to terminate the Excel.exe process by getting the handle of window
Used this code for the function

Public Function open_excel()
Excel.Workbooks.Open "D:Process Termination emplate.xls"
' My code
Workbooks.Close
Dim lHwnd As Long
lHwnd = FindWindow("XLMAIN", vbNullString)
ProcessTerminate , lHwnd
End Function

If i terminate the process it is not able to open excel the next time through code and the code is giving error. Can anyone help me out. Thanks in advance.
Sameera

Text Files Opened In Excel
Hi,
Im having a bit of trouble with some code that im using to open up a certain text file in excel. The text file i'm opening consists of many strings of numbers, these will then be formatted by the program once the file is open in excel. I'm currently using this as the code to open the text file:

----------------------------------------------------------------
Workbooks.OpenText FileName:= _
Location + "" + File _
, TextQualifier _
:=xlDoubleQuote, Comma:=True, FieldInfo:=Array(1, 2)
----------------------------------------------------------------

The problem is this. If i use the address as the argument for the filename (e.g. filename:="c:/folder/thetextfile.txt") excel opens it as text, if i use the code above however (where it determines the file location)excel opens it as some kind of number format (the output is something like 12312 + E12) and it cuts off any leading zeros.

An example of the data in the text file:

-----------------------------------------------------------------
1234567890123123
0001230022828998
0039892183900000
-----------------------------------------------------------------

Does anyone know how i could solve this?
Thanks
Matt

Manipulating A Program Opened With Excel
Well, i figured out how to open a telnet program using excel and VB, now i need to be able to enter data fields and click the "ok" button from my code.

How To Test An Excel File Is Already Opened?
hello,
I want to modify an excel file and before doing this, I want to test it is already opened or not. So I write the codes as following, but it does not work.

On error goto ERROR_OPEN
Open MyTestFile For Binary Access Write As #1
close #1

ERROR_OPEN:
If Err = 55 then msgbox "File already open"

if MyTestFile is already opened, excel xp will open it with read only mode and do not generate the error 55.
Thanks a lot.

[DAO] Excel Sheets Always Exclusively Opened?
I've made a program that uses an Excel sheet as a database. The problem is that this Excel sheet is somehow always exclusively opened...

This is the code I use to open the Excel sheet:


Code:
Dim ExcelSheet As Database
Dim ExcelSheetRecordSet As RecordSet

Set ExcelSheet = OpenDatabase(ExcelSheetPath, False, False, "Excel 8.0;HDR=yes;")
Set ExcelSheetRecordSet = ExcelSheet.OpenRecordset(ExcelSheet.TableDefs(0).Name)

Why o why is this excel sheet exclusively opened

Excel - Reading An Already Opened File
I want to be able to read the cells of an excel file that is currently running, without knowing the path name, basically just go on the excel screen and do some validation work. Is my only option somekind of findwindow and even at that, once I find the instance, how can I then read/edit what is on screen?

Closing An Excel File Opened In VB
I open an excel file, stuff in some data, close it and kill it.

everything works fine but the close process brings up a system messge asking if I"d like to save the changes. I'm sure that I've seen a way to make the close process tell the system to save without asking, but now I can't find that technique anywhere in
the on-line help, so if anyone knows it, I'd appreciate the syntax.

my close is just:

xlApp.Workbooks.Close

and I think it can be something like

xlApp.Workbooks.Close vbSaveWithoutAsking

(you get the idea)

thanks

How Can I Close Excel File If It Is Opened Already Using Vb
how can i close excel file if it is opened already using vb ...?

Save And Close Opened Excel App
I need to close my excel spreadsheet while unloading the form (if its open).
and i used below code.. it works fine... for some extent.

***************
Public Sub DetectExcel(strFileName As String)
Dim hwnd As Long
Dim objXl As Excel.Application
Dim objWB As Excel.Workbook
Dim i As Integer, cnt As Integer
hwnd = FindWindow("XLMAIN", 0)

If hwnd <> 0 Then
Set objXl = GetObject(, "Excel.Application")
cnt = objXl.Workbooks.Count
If cnt > 0 Then
For i = 1 To cnt
Set objWB = objXl.Workbooks(i)
If objWB.Name = strFileName Then
objWB.Application.WindowState = xlMaximized
objWB.Close SaveChanges:=True
Exit Sub
End If
Set objWB = Nothing
Next
If objXl.Workbooks.Count = 0 Then
objXl.Quit
Set objXl = Nothing
End If
End If
Set objXl = Nothing
End If
End Sub
**********************
Problem comes when i have more than one excel apps running and open at the same time. The FindWindow always returns the handle of first available excel app ( i donow how does it choose though) , if i loop thru all the available excel apps and get the handle of each, my purpose is solved, but donnow how to do. Help appreciated.

Thanks in Advance

Fill A Vba Form With Excel Workbook Data, Updating The Same Workbook W/ Data From The Form
Okay I have a basic to intermediate understanding on using vba and Excel utilities. I need help with the workflow of my application rather than specific procedures.
I've got data on only one worksheet where each record is defined by an account id. Every cell on each record is populating a specific textbox on a form. The form is a basic interface for users, moving from one record to next. So mostly the data will flow from the worksheet to the form with the exception of one field for each record. The user will update that field as needed on the form. There are only 30 records or so on the worksheet. I can't rationalize learning Access and/or some database connection since there are only a few records and few fields.

This is an example of the field headers and one record:
Account Id, Account Name, Total Balance, Past Due Balance, Status
011,ChevyChaseAccount,$1200,$100,Customer sent in check (not posted).

I can think of only one way to update the form with the data and that is piece by piece:
Code:Private Sub UserForm_Initialize()
    With frmDetail
        '.txtAccountName = ActiveSheet.ActiveCell.Offset(0, 2).Value (this gives me an error since I can't use the offset property for the code in forms)
        .txtAccountName = ActiveSheet.Range("B2").Value (since I can't use offset then I have to resort in a specific address)
        .txtAccountId = ActiveSheet.Range("C2").Value (since I can't use offset then I have to resort in a specific address)
        .txtTotalBalance = ActiveSheet.Range("D2").Value (since I can't use offset then I have to resort in a specific address)
    End With
End Sub

I haven't thought of a way to move from one record to another let alone how to update that one field to another.
Let me know if you have any questions,

Access Form Opened With VB
HI, Here is the situation...


I created an inventory database using only ms access. I am now creating a quote tracking system that is written in VB with an access backend. My problem is that I need to open a form in my inventory access database using visual basic code. I want to have a search button in my new program that will enter values into an access form and then activate a button on the form. Is this possible??? I hope.

Thanks.
Brandon

Access Database Cannot B Opened?
Greetings every1,
I'm having Office XP, and used Access to create a DB. Afterwards I used the following code to open it.

Code:
Dim DB As Database
Dim RS As Recordset
Private Sub Form_Load()
Set DB = OpenDatabase("C:db1.mdb")
Set RS = DB.OpenRecordset("table1")
Text1.Text = RS.Fields(0)
End Sub
It gives the error "Unrecognized database format". Why is this. If I create a DB from within VB's Visual Data Manager, it works fine. What shld i do. My OS is Win2k...

Server Has Not Yet Been Opened - Access && CR8.5
EDIT: I have a different problem, but the EDIT function won't let me change the title of the post...sorry for the confusion.

I set up a CR8.5 report using ADO and an ODBC connection. In my VB code, I do the following and I get the message "File Not Found":


Code:
Public Sub OpenReport(ReportName As String)

Dim crxApp As CRAXDRT.Application
Dim CrSections As CRAXDRT.Sections
Dim CrSection As CRAXDRT.Section
Dim newObject As Object
Dim crxTable As CRAXDRT.DatabaseTable
Dim crObjects As CRAXDRT.ReportObjects
Dim crxSubObj As CRAXDRT.SubreportObject
Dim crxReport As CRAXDRT.Report
Dim crxSub As New CRAXDRT.Report

Dim rs1 As ADODB.Recordset
Dim sSQL As String

'set a new instance of the CRAXDRT application
Set crxApp = New CRAXDRT.Application

'open the main report
Set crxReport = crxApp.OpenReport(App.Path & ReportName, 1)

'ADO connection, query and rs setup
sSQL = "SELECT * FROM Departments, Employee, [Employee Training] " & _
"WHERE Departments.DNUM = Employee.DNUM AND " & _
"Employee.[CLOCK#] = [Employee Training].[CLOCK#]"

Set rs1 = New ADODB.Recordset
rs1.Open sSQL, cn, adOpenStatic

'loop all tables and verify the datasource
For Each crxTable In crxReport.Database.Tables
crxTable.Location = DB_Path & "Training.mdb"
Next

'bind the datasource to the main report
'release memory of any saved data on the entire report
crxReport.DiscardSavedData
crxReport.Database.SetDataSource rs1
Debug.Print crxReport.Database.Tables(1).DecriptiveName

'Get the sections from the Main report
Set CrSections = crxReport.Sections
Dim i As Integer
'Go through each section in the main report...
For Each CrSection In CrSections
'get all the objects in this section...
Set crObjects = CrSection.ReportObjects
Next CrSection

CRViewer.ReportSource = crxReport
CRViewer.ViewReport

End Sub

The report contains 3 tables linked together and it refers to one of them as "ado" (the alias)...could that be the problem? Because in my query, I use the actual table names instead? If I change this alias it causes a whole lotta problems in the report...

Find Out If An Access MDB Is Opened
I have a button on my app that opens a Access MDB archive. I'm using the ShellExecute function to open it. But I want that my app check if the MDB is already open, case it is it would just change the focus.

Thanks!

Access Database Cannot B Opened?
Greetings every1,
I'm having Office XP, and used Access to create a DB. Afterwards I used the following code to open it.

VB Code:
Dim DB As DatabaseDim RS As RecordsetPrivate Sub Form_Load()Set DB = OpenDatabase("C:db1.mdb")Set RS = DB.OpenRecordset("table1")Text1.Text = RS.Fields(0)End Sub


It gives the error "Unrecognized database format". Why is this. If I create a DB from within VB's Visual Data Manager, it works fine. What shld i do. My OS is Win2k...

Giving An Opened Excel File Another Name And Saving It.
Hello,

I've the following question:

In my VB application I've opened an excel file (for example "prijslijst.xls). I make some changes and then when I close the exceldocument(by pressing the "x"upper right corner) I want to save the document under another file name (for example "prijslijstklant1.xls", with klant1 as a string ) I can't find the right code to program this problem. Can somebody help me a little bit?


Greetz,

Closing Previously Opened Excel Instances
This is the first problem that I haven't been able to resolve while searching...

I wrote a program in VB6 that opens excel (2003), does its business and then closes & saves every workbook that it's either opened or created. It works like a dream except for one little flaw.

If there is an excel document already running that the program intends to modify it will open the document, make the necessary changes and try to save & close. That causes quite a problem when it stops and asks if you want to overwrite. I need it to be completely hands off.

What I would like to do is check if there are any instances of excel running on startup of the program. For any open workbooks, it would be nice if it could save them and then kill every excel instance that is running. If that's a little too much then I wouldn't mind killing every thing off without saving. There isn't supposed to be anyone messing with excel manually anyway.

That's one way of doing things.....

My other option is to check to see if the workbook to be modified is already open. If it is then set my xlApp = to that excel instance and xlBook = to that particular workbook. Come to think of it, that would be much better than killing excel before hand and losing any info that might be critical.

Any help with this would be greatly appreciated.

-asromzek

I Want My VB App To Close An Excel Sheet If The User Has Opened It.
My app aquires some data then dumps it to Excel, no problems. Then the user may want to open the sheet and look at their data, no problem with that either. This is all working, but I want to add something to my code to check/close the sheet if the user opened it before they start another acquistion cycle. Here's my declarations and usage of the Excel objects.


Code:
Dim objExcel As New Excel.Application
Dim objBook As New Excel.Workbook

Then the first data run:

Code:
Set objBook = objExcel.Workbooks.Add
...
objBook.SaveAs Filename 'Filename is user defined
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing

Then next data run:

Code:
objExcel.Workbooks.Open Filename
...
objExcel.ActiveWorkbook.Save
objExcel.Workbooks.Close
objExcel.Quit
Set objExcel = Nothing

So, the user may have opened Filename in between runs. If this happens, and they leave it open, their 2nd run data will not be saved. I've tried a few things and have not had success.

Vbcode To Save Opened Excel Document
Hi, anyone knows how I can save an already opened excel document?
I already did it with a Word document, using this code:

Dim wdApp As Word.Application
Set wdApp = Word.Application
Word.ActiveDocument.SaveAs strFilepath

My code for the Excel document is:
Dim xlApp As Excel.Application
Set xlApp = Excel.Application
xlApp.ActiveWorkbook.SaveAs strFilepath

I get the following Error, only with Excel document:
'Run Time error 91': Object variable of with block variable not set
In debug mode, I can also see that ActiveWorkbook = Nothing,
while I have certainly opened an active Excel document at this point.

I do exactly the same with a Word document, and this runs
perfectly, here ActiveDocument = Document1

Any help is appreciated!

VB With Excel COM Object: Hiding Opened File
Hi everyone, thanks for looking.

I am using VB6 (not vba) with the Excel com add-in.  I take a copy of the spreadsheet and move it to the Windows temp directory so the original can be opened by the user while my program accesses the slightly renamed copy.

My issue is that if the user opens the original, Excel windows for both the original and the copy are opened.  Is there a way to hide the window for the temp spreadsheet my program is using? ____________________________________________________________________________
There are only 10 types of people in the world.  Those that know binary and those that don't

Brent

Locking Excel File So It Cannot Be Opened While Processing
My little app will create a new spreadsheet from a template. There are two issues I have:

1. If someone open the new file while my app still processing/importing data, then the app will crash. Is there a way to lock the file so it cannot be opened (or can only be opened read-only). Or maybe there is a better way than .save?
Code:Public Sub Init(ByVal TemplateFile As String, ByVal ReportFile)
    Set oBook = oExcel.Workbooks.Add(TemplateFile)
    oBook.SaveAs ReportFile
    'processing
    'oBook.Save
    'processing
    'oBook.Save
    ....
    'oBook.Save

2. Is there a "best practice" to avoid ghost excel process (in task manager) when the app crash? What kind of error handling is considered "best practices?"

Thanks!

How To Make An Excel Macro Run Upon Its File Being Opened?
I have an Excel macro whose execution needs to be automated. It would seem simple - if DocumentA is opened, then make MacroA run. Can a macro be attached to the opening of its file?

...or, do I need to somehow export the code of the macro into a .exe file (is this possible???). I am a bit lost on the Windows platform, and have been working in the XP environment for about a month. I know a lot more about getting things like this done on the Mac OS than in Windows, so please consider that. I just learned what a .bat file was about 2 weeks ago.

Thanks,

Bill

Load Form When MS Access Is Opened
is there anyway to open a form as soon as MS access file is opened

Access To File Opened By An Application
Hi,
I wqs wondering if it was possible to get the file handle of a document opened by say MS word.

TIA

How To Check Access Database Opened
I have done a software in visual basic with access database as backend. I have stored my software password in the access database and the database is also protected by password. But hackers have opened the access database and easily find out the software code. It is possible to check whether the database is opened by the hackers.

How To Close All Opened Access Connections
Hi all,

 I have a VB.NET and MS Access database application. I neet to compact the database and I have a code that works OK for that but somwhere in my program that is now about (35 froms) I must have left a connection open because if I play about with the program for a while and then try to compact db using this code :

Code:
Dim db As New DAO.DBEngine

        Dim strPath As String = Application.StartupPath
        db.CompactDatabase(strPath & "asbestos.mdb", strPath & "BeechwoodD.mdb")

 

I get error message that this DB is already opened

Therefore I am looking for a piece of code that will close all the opened connections to the database

anybody can save me from going over 35 forms ?

Cheers guys

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

<Signature>

     That boy is our last hope.......................................

     No there is another !

</Signature>

How To Check Access Database Is Opened
I have done a software in visual basic with access database as backend. I have stored my software password in the access database and the database is also protected by password. But hackers have opened the access database and easily find out the software code. It is possible to check whether the database is opened by the hackers.

Copyright © 2005-08 www.BigResource.com, All rights reserved