Vb And Excell
i want to work from vb with excell as my DB!
is it possible?how can i use it?
can i make queris on it?
thnaks in advance
peleg
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
VB And Excell W/out Having Excell Loaded
Can I use a program that retreives data from an Excel speadsheet on a machine that does not have Excel loaded?
Also, when I went to the references to set "Microsoft Excel 10.0 Object Library", it was not listed. Any ideas as to why it was not listed?
Any help or suggestions would be grealty appreciated.
Excell In VB 6
hey i am writing a program where i need to display an excell sheet and user form at the same time. the users make selections on the form with clicks. the info needs to be sent to particular cells on the sheet. i am using MDI with 2 forms one for the selections one for the sheet. i tried using the excell control but i can't seem to reference particular cells on the control. am i going about this the wrong way? there has to be a better way. i am a student so be easy.
Excell && VBA Help Please
Hello,
Please help. What I am trying to do, is grabbing a drawing name in Cells(x,2), automatically search the last modify date in C drive, and post the date in Cells(x,3). The current code works fantastic for me (Thanks for the helps from people in this forum), and currently I am just wondering for more options.
QUESTIONS:
1. Is there any way to automote searching my drawing file without having to list all my strPath? So i can just write the location is in "C:drawing able"
or this is even better if i can just list "C:drawing"
Can i apply this?
With Application.FileSearch
.NewSearch
.LookIn = "C:drawing able"
.SearchSubFolders = True
.FileName = "Run"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
End With
2. Is there any way to put a browse button, so if it is not the right path, then i can browse & locate a new path location and enter it into the inputox
3. Because my folder locations for current & future drawings are in a separate folder, but still in the same sheet, i have to split up the code to
run from x=1 to 20 for CURRENT drawing & from x = 21 to 55 for FUTURE drawing)
Everytime i add more drawing files into the current or future drawing, i have to modify the x value and change the range. For a lot of updates, this is painfull'
Is there any VBA code that can distinguish current & future drawing. So when it scans cells (x,1) and finds a "FUTURE" word, it knows
that it's time to run the code from a different folder & popup the MsgBox.
Thank you for everybody who had been helping me before. I really appreciate your help.
ps: The questions above are also posted inside the code.
Excell File
1 2 3
A Status Name Last Updated
B CURRENT drawing1 11/12/2001
C drawing2 12/06/2001
D drawing3 30/03/2001
.
.
T FUTURE drawinga 12/04/2002
U drawingb 14/08/2002
CODE:
Private Sub Workbook_Open()
Dim strX As String
Dim strF As String
Dim strPath As String
Dim strFile As String
Dim oFile As Object
Dim FSO As Object
Dim intSheetIndex As Integer
'--------CURRENT-------------
strPath = "C:drawing ablecurrentmodel_A"
strExt = ".dwg"
'''QUESTION 1 ''''''''
' Is there any way to automote searching my drawing file without having to list all my strPath? So i can just write the location is in "C:drawing able"
' or this is even better if i can just list "C:drawing"
' Can i apply this?
'
'With Application.FileSearch
' .NewSearch
' .LookIn = "C:drawing able"
' .SearchSubFolders = True
' .FileName = "Run"
' .MatchTextExactly = True
' .FileType = msoFileTypeAllFiles
'End With
'
'
''''''''''''''''''''''
Set FSO = CreateObject("Scripting.FileSystemObject")
If MsgBox("Is the folder location of " & strPath & " the correct path?", vbYesNo, "Confirm Path") = vbNo Then
strPath = InputBox("Please enter a new path bellow:", "New Path", strPath)
End If
'''QUESTION 2 ''''''''
' Is there any way to put a browse button, so if it is not the right path, then i can browse & locate a new path location and enter it into the inputox?
'
''''''''''''''''''''''
If FSO.FolderExists(strPath) = False Then
MsgBox "Invalid path (" & strPath & ")" & vbCrLf & "Macro cancelled!", , ""
Exit Sub
End If
intSheetIndex = 1
' ********
For x = 1 To 20
' ********
With Application.Sheets(intSheetIndex)
strF = .Cells(x, 2) & ".dwg"
If Len(strF) > 7 Then
strX = Mid(strF, Len(strF) - 3, 4)
If strX = strExt Then
strFile = strPath & Trim(strF)
blnExists = FSO.FileExists(strFile)
If blnExists = True Then
Set oFile = FSO.GetFile(strFile)
.Cells(x, 3) = oFile.DateLastModified
.Cells(x, 3).Font.Color = vbBlue
Else
.Cells(x, 3) = "File Not Found"
.Cells(x, 3).Font.Color = vbRed
End If
End If
End If
End With
Next
Set FSO = Nothing
End Sub
'----- FUTURE -----------
strPath = "C:drawing ablefuturemodel_B"
strExt = ".dwg"
strExt = ".dwg"
Set FSO = CreateObject("Scripting.FileSystemObject")
If MsgBox("Is the folder location of " & strPath & " the correct path?", vbYesNo, "Confirm Path") = vbNo Then
strPath = InputBox("Please enter a new path bellow:", "New Path", strPath)
End If
If FSO.FolderExists(strPath) = False Then
MsgBox "Invalid path (" & strPath & ")" & vbCrLf & "Macro cancelled!", , ""
Exit Sub
End If
intSheetIndex = 1
' ********
For x = 21 to 55
' ********
With Application.Sheets(intSheetIndex)
strF = .Cells(x, 2) & ".dwg"
If Len(strF) > 7 Then
strX = Mid(strF, Len(strF) - 3, 4)
If strX = strExt Then
strFile = strPath & Trim(strF)
blnExists = FSO.FileExists(strFile)
If blnExists = True Then
Set oFile = FSO.GetFile(strFile)
.Cells(x, 3) = oFile.DateLastModified
.Cells(x, 3).Font.Color = vbBlue
Else
.Cells(x, 3) = "File Not Found"
.Cells(x, 3).Font.Color = vbRed
End If
End If
End If
End With
Next
Set FSO = Nothing
End Sub
''' QUESTION 3 '''''
'
' Because my folder locations for current & future drawings are in a separate folder, but still in the same sheet, i have to split up the code to
' run from x=1 to 20 for CURRENT drawing & from x = 21 to 55 for FUTURE drawing)
' Everytime i add more drawing files into the current or future drawing, i have to modify the x value and change the range. For a lot of updates, this is painfull'
' Is there any VBA code that can distinguish current & future drawing. So when it scans cells (x,1) and finds a "FUTURE" word, it knows
that it's time to run the code from a different folder & popup the MsgBox.
Thank you for everybody who had been helping me before. I really appreciate your help.
Again Help; VBA Excell
Hello Guys,
Please help out. What I've been trying to do is getting the drawing file in my local C drive (Cells(x,1) or D1, E1, F1,..M1), retrive the date, and then automatically post the date on the next cell (Cells(x,2) or D2, E2, ...M2)
------------------------------------------
Excell File:
1 2
A Drawing File Last Updated
B
C Table
D drawing1 12/12/2001
E drawing2 12/12/2001
F drawing3 12/12/2001
G
H Chair
I drawing4 12/14/2001
J drawing5 12/15/2001
K
L Other
M drawing6 12/18/2001
---------------------------------------------
Current Code:
Sub ShowFileInfo()
Dim fs As Object, f As Object
Dim x As Integer, FileName As String
For x = 4 To 6
FileName = "C:drawing able" & Cells(x, 1).Text & ".dwg"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(FileName)
Cells(x, 2) = f.DateLastModified
Next x
For x = 9 To 10
FileName = "C:drawingchair" & Cells(x, 1).Text & ".dwg"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(FileName)
Cells(x, 2) = f.DateLastModified
Next x
For x = 13
FileName = "C:drawingother" & Cells(x, 1).Text & ".dwg"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(FileName)
Cells(x, 2) = f.DateLastModified
End Sub
--------------------------
Condition:
- Every drawing file (drawing1, drawing2, ..., drawing6 is hyperlinked to an internal website)
--------------------------
Right now i have to specify a location of x for each file as you can see in my code. If it scans a blank cell, it will return an error message. For a lot of drawings, this is painfull. =)
Priority 1:
is there any way to scan automatically ALL Cells(x,1) (including the blank one and the title) to get ONLY the hyperlinked file and then post the result on Cells(x,2)?
Priority 2:
How about if i change the name format of my drawing file in excell by adding .dwg file extension (drawing1.dwg, drawing2.dwg, ...drawing6.dwg) so that the computer can just scan only this file and skip blank or non .dwg extension?
Please help. Thank you
Excell && VBA Help Please
Hello
I have a lot of drawing files, which are stored in my local C drive. Currently, I use excell to list the name of the drawing files' from A1:A50 spreadsheet rows . From B1:B50 rows, I put the date when the last time I save it (which can be found on the property of the drawing). The problem is i have to change the date in excell manually everytime i update/save the drawing. Is there any VB script that will automatically read the file name, find in the local drive, grab the last updated date, and put it on the excell? Please help. Thank you in advance for the help
Excell And Vb
Is it possible to save everything on a form (data in combo boxes) to an excell sheet and be able to load it and save it as a seperate file each time
Using Excell
Can someone please post a simple working example of a VB program that takes an entered value and puts it into an excel file, and then saves the file. Also how to empty the contents of an array into a column and then save it.
I have found a few examples but have not been able to figure out how the code works, much thanks in advance.
~ Bryan J. Casler
Vb And Excell As A DB
i want to work from vb with excell as my DB!
is it possible?how can i use it?
can i make queris on it?
thnaks in advance
peleg
Regarding Ms Excell
hi,
I am accessing excel having some rows........i am storing value of each row in a array.......how can check whether last row is reached?
thanks and regards
vivek.s
Regarding MS Excell
hi,
i am using (oApp.Sheets(1).UsedRange.Count) / no of cols(in my old version before GURU gave me another solution)...........to find no of rows.....now i deleted 1 row in excel and re ran the program.......still it counts the same no. of rows as it initially did.......any way to rectify this?
Thanks
S.vivek
VB-Excell
I am woking with VB and Microsoft Excell. To work with Excell I have included Microsoft excell from reference.It is just updating some cells based on some conditions.It is working fine in stand alone machine.
My problem lies in networking scenario.
The problem is that our excell file is kept in a machine say server.Machine1 and Machine2 is accessing the server to update the excell database(Every 1 min interval) .When the time of updation by two machines are becoming the same,the server machine is getting hanged. I think some sort of locking is necessary so that other machine can't update that when one machine is accessing the excell database.Please give me some solutions.
Excell EOF
Hi,
I have to write an app that imports an excell spreadsheet into a database.
Does anybody know how I can test for EOF on an excell spreadsheet. At this stage I test for blank cells but this is not working for all scenarios.
Any help would REALLY be appreciated
Excell
hello,
i'm using a flexgrid to hold data. now i want to be able to export this to excel and able to import it back up.
can anyone point me in the right direction?
thanx
Excell With V.B
HI
I've a application that use with excell object
(Insertable objects - microsoft excell)
How can I add my own sub menu to the excell menu?
For example : between "View" and "Insert" I want to add
"Functions"
Thank you
Excell And Dao
Hi hope someone can help me, i need to write some values on a exsiting excell sheet how can i open it and write in a specific cell?? i have seen the examples for doing it on a new sheet but i cant find how to do it on an existing one, hope someone could help me thanx.
Using VB In Excell
Can anybody recommend a good book that will teach me how to use VB within microsoft excel?
Excell
I am using this code:
Set objChart = OLE1.object.ActiveChart
It's work on my computer but if I make a set up from this code and install it in a computer that does not have excell, I get
run time eror 91 .
what should I do ?
Excell
Any Tutorials on Reading / Writing To Excel Work Sheets?
Set Excell = Nothing?????
Hi, I am manupolating an excell spread**** in my program but at the end when I say set objexcell = nothing.. It doesn't do anyting.. I mean it doesn't close the object and next time that I want to run the sub it says that the workbook is in use. Anyway to force the object to close??? Thanks in advance
VB 5.0 And Excell
Can anyone suggest the best way in which I can save data be it from a text file, ado recordset, or whatever source to an Excel spreadsheet. Do I have to open an instance of a spreadsheet or a workbook ??? I'm confused. Any help greatly appreciated
Steve
VB Programmer
Steveflit@aol.com
A Sub In Excell
Hello,
How can I activate a "Sub" (the sub is located in a module) in an Excell workbook with a vb5 aplication.
for example:
this is Sub in Excell
Sub print_plan()
Call dbShopOrder.SearchOrders
Worksheets("plan").Activate
ActiveSheet.PrintOut
End Sub
greetings R@emdonck
[This message has been edited by R@emdonck (edited 12-11-1999).]
A Sub In Excell
Hello,
How can I activate a "Sub" (the sub is located in a module) in an Excell workbook with a vb5 aplication.
for example:
this is Sub in Excell
Sub print_plan()
Call dbShopOrder.SearchOrders
Worksheets("plan").Activate
ActiveSheet.PrintOut
End Sub
greetings R@emdonck
[This message has been edited by R@emdonck (edited 12-11-1999).]
VB + Excell
I'll admit im pretty much a VB noob so I may be asking a stupid question. My problem is I need to alter an existing Excell sheet quick and easy as it needs to be done weekly.
I want to record a macro in excel to do it however, I dont know how to write the VB to do a portion of the required steps.
The meat of my problem is this...
I need to indentify if certain columns have information, in the form of a date, located in them. If they do, I need to remove the information from said column as well as remove a monetary value from a different column. I also need to subtract that monetary value from the total at the end. BTW, this is for many, many rows of data. If it were only a few rows I wouldn't worry with trying to build a macro or a faster way to handle the change.
Other steps I need to take is simple, remove a whole column and recalculate the total after any deletion.
I was considering using an If/Then but as far as VB is concerned I honestly dont know where to start. Any advice would be greatly appreciated.
Will
Excell Help
This forum is the closest i can find for Excell help, so apologies if slightly off topic.
If i have a number of columns, one column containing a finacial value, the other containing sales peoples initials, and i need to count how many times a sales person has an account with a value greater than zero.
What formula do i need to use?
Many thanks and kindest reagrds
Howard
VB And Excell Help
Hi
Can anyone help, I need to get data from access to vb, and then onto a excell chart from there, tried sending a screenshot, but no luck, so if anyone can help, I'll email the screenshot through to you.
Thanx
Koos
Excell And Vb
What i've done so far is i export my data from an pervasive database to excell but now i need to caculate the sum of all the duplicate entries and export that to excel so that i will only give me the customer number the name and the sum of all the stuff he brought for the specific month.
Vb - Excell
Hi, i need to create excell histogram chart from BV6.
I do not have experience with this.
Can you give me code sample or dirrection.
Thanks'
Excell
Hi all,
i have data on Excell and i want to move it to access database.
can i do it and how?
Thanks,
Help Me, Please: VBA Excell
Hello i'm beginner In VBA Excell, and was necessary to create two scripts under Excell:
1. for numerical integration
2. for numerical differentiation
Please, help me.
Hmm Excell Or Rtb?
I need to know how to input things into a richtext box that are in a list view. I know how to do it but it looks really ugly . Does anyone now how to line it up so it looks like it does in the list view. Oh if your gonna tell me to export it to excell that's already been done this is for ppl witkout excell loaded.
can u please help !!!!!!
thanks
Excell
i am writing a macro for an excell workbook. I need to write a line of code that will check if a worksheet exists, and if it doens't will add it. I have no problem adding it if it does not exist, but cant come up with a way to check if it exists? If it already exists i get an error for trying to name a new worksheet the same as an exising one. I'm looking for something that will work like this
If "my worksheet does not exist exists" then
Sheets.Add.Name = "Week Summary"
End If
Thanks,
J
Excell
I am filling excel worksheetts' cells from macros,
using something like:
Worksheets("sht1").Cells(1,1)=7.
I want to give to this cell, the patterns & border of another cell, say cell :
Worksheets("sht7").Cells(iline,jcolumn)
I have to use (iline,jcoulmn) to point to the cell I copy the patterns & border from.
How do I do it?
(I use excell 97)
Excell
I am filling excel worksheetts' cells from macros,
using something like:
Worksheets("sht1").Cells(1,1)=7.
I want to give to this cell, the patterns & border of another cell, say cell :
Worksheets("sht7").Cells(iline,jcolumn)
I have to use (iline,jcoulmn) to point to the cell I copy the patterns & border from.
How do I do it?
(I use excell 97)
VB6 - Excell && Access
Hi, Firstly Many Thanks in advance for any help/comments.
I have a VB6 application that basically adds data to a database (access). Part of this information is a date in numerical form Ie DD/MM/YYYY. What i would like to do is take this date from the VB6 app and add it to a Excell sheet. So the sheet would have a monthly view of days acros the top say 1,2,3,4 and so on. What i would like is to take the date on my app say the 1/mm/yyy and put a 1 underneath the 1 on the excell sheet. Basically i want a excell sheet listing orders on specific days and i want to add the order date using the application.
I used to run a query to see how many orders within two dates, which worked fine, but now i would like to look at a sheet of 1 month and visually see when the orders were placed ( if this helps explain)
Thanks for any help/input
Paul
Is This Possible? : Excell With Adodc
well...in this part of the forum:
http://www.xtremevbtalk.com/show...threadid=59497
i tryed to ask something like this, but now i'm trying also to connect the adodc with excell files and it gives me an error:
"Problem with FROM..."
i use:
"Select * FROM Sheet1$ order by Column1"
what's wrong??
can someone put here an example?
thanks!
Excell && VBA Search Help
Hello,
Is there any additional vba code that can help me search my drawing file without having to list a full path/location of the drawing file in my drive C? so can just list BaseDir = "C:" and the macro will automatically to search all sub folders in drive c: until it finds the file that i list on excell.
code
Sub ShowFileInfo()
Dim fs As Object, f As Object
Dim x As Integer, FileName As String
Dim BaseDir As String, CurrDir As String
Dim PicName As String
For x = 3 To 4
BaseDir = "C:drawing able"
PicName = Cells(x, 1).Text
CurrDir = BaseDir & PicName
FileName = CurrDir & ".dwg"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(FileName)
Cells(x, 2) = f.DateLastModified
Next x
End Sub
Please help. Thank you
ps: in excell help, there is a getabsolutepathname command and
With Application.FileSearch
.NewSearch
.LookIn = "C:My Documents"
.SearchSubFolders = True
.FileName = "Run"
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
End With
But, i don't know if this will work in my case
Microsoft Excell
I am a comlete beginer! How do I referance a cell in microsoft excell work sheet to save the contents of the cell in to an array.
I think very roughly it will be somthing like like this except in more detail.
contents of Array cell (0, 9) = excell work sheet cell no. (C, 22)
HELP; Excell Formula Or VBA?
Hello,
My current task is I always update my drawing file every 2-3 days (autocad drawing files), save it in my local server under the same name, and change the date in the "last update" column manually. The drawing file is hyperlinked to be viewed locally online , so i can just double click the file to open and save it.
The files are so many. Sometimes, i got screwed up and don't remember when i update the drawing files after i update them. Therefore, I am just wondering if you can help me out. I feel that your skills are much better in excell & VBA. Is there any way in excell formula or VBA to automatically change the date in "last update" section automatically when I double click to open the drawing file or after i save the drawing file?
Thank you in advance for your help.
ps: example
drawing file name last update Approved?
car1 12/12/01 yes
car2 11/10/00 no
car3 12/22/99 yes
Excell PLease Help!! Quickly!!
Can anyone please tell me how to manipulate an Excell spreadsheet as you would an Access database?
Is it possible to use DAO?
Thanks
Excell And Stuff...
Well, I've finally subscribed in here...
Found a lot of things about Excell programming in VB, but there is something I can't find : a listing of all the Excell objects and their properties... It would be a lot easier to find answers with a list like that.
My current problem is this : I work with Access tables to keep my data, and I would like to print them in a nice way. Instead of programming the Printer object it's much easier to import the access data in an excell template I made. Now everything works fine except:when the printout is finished and I call 'xlApp.quit' and 'set xlApp = Nothing' it always asks me 'Save changes to ToDoTemp.xls?'... Offcourse NOT, but how do I make it clear in my code that the object can be killed without saving the changes? (We don't want the users of my app to see the excell dialog box don't we?)
Thx for any help offered.
Noah.
VB Access To Excell
Can anyone help me find a programmatical way to avoid having an excel workbook be hidden when data is transferd. I have created an MS Access Database that can transfer various information into excell spreadsheets, but when the spreadsheet file is opened the info is hidden. Other then manually going and clicking unhide is their any code that i can use to permenetly solve this problem?
Save As Something Else (Excell).
Hi all
Here's what I need to do. I need to open a model .xls and then add items to it then save it as something else .xls somewhere else.
I cannot find anything that would help me.
Any ideas if I can do this?
Thanks
Lee.
Excell Question
I'm not sure if you need VBA for what I want to do, is there a way to automaticly have the data in a cell on one worksheet added to a cell on another worksheet when the data is changed on the first worksheet?
Excell Selections
hi, ive got some code that i got by using the macro recorder. trouble is that it selects a cell before doing an action on the active cell. is there a way to do this bit of code without using a selection?
Code:
Sheet1.HPageBreaks.Add Before:=ActiveCell
also ive got something to do with text.
Code:
ActiveCell.Characters(Start:=1, Length:=2).Font
how do i replace the numbers with variables cos the start and length things arnt strings by the look of it so i dont know how to add something to the end of one. not like "Start:=" & intstart
Excell Dump
I was wondering if anybody knew how to make a program that extracts a doc file and places it into a excell spread sheet. I would like to get a rather large document (actually it is a few hundred pages) that is in a very specific format. I plan on systematically inputting the document into a text box and then spitting it out into an excell spreadsheet, and this is where I am encountering a problem/
I would like to populate an excell document by a whole buch on string format functions, like each row is determined by vbCliff.
I would like the formating to be something of this nature
Rows: I would like the rows to be defined by line carriages. So that everytime there is a line carriage I would like excell to start to write on the next row.
Columns: I would like the computer to read from left to right and count character spaces so the first 20 charaters are to be put in column 1, the next 20 in coulumn two and so fourth and so fourth.
I have never inputed into an excell spread sheet so any help would be great (code snipits would be nice).
Thanks in advance.
EXCELL VB MACRO
I am not a VB programmer so I could use a little help with an EXCELL macro in VB.
What I am trying to do is to use the EXCELL VB MACRO to save the EXCELL spreedsheet and give it the name of the contents of CELL A1.
The following is the code I have which doesn't work but hopefully it is in the right direction. Any suggestions would be appreciated.
-------------------------------------------------------
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/25/2001 by tneumann
'
ActiveWorkbook.SaveAs Filename:="H: neumannExcell_files " & Range("A1").Select, _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
VB6 - Excell Sheet
Hi,
I was wondering, if I use Excell sheets in Visual Basic, does the user, that will later be using the program, need Excell installed on his/her computer?
MS Excell/database
I need to create a button/object that can be attached to the excell spreadsheet that can be macro assigned to capture its data to its assigned excell database.If I manualy use file-menu-save it works fine.Or if I cntrl s it works fine, but if I assign a macro it doesn't prompt to update the database. I must be missing something????
Like Excell List
Hi
how can i find a manual filtered listview or something like excell.
Number Name Address
[All numbers] [All names] [All adresses}
1 Tom CA
2 Jane SA
3 Bill NY
for example i ll click All numbers cell and i ll select 1 and it ll show me just number "1". I saw in Delphi but i cant find for VB.
|
TRACKER
