Posts by Ped

Hi, I have the code from this link...Cool! but i need to know how to place a password line as my access database is password protected.

Please help me out here...

Thanks for helping in advance.


Pedie
link http://www.ozgrid.com/forum/showthread.php?t=19625&page=1&

[/FONT][FONT=Courier New]Option ExplicitSub Import_AccessData()Dim cnt As ADODB.ConnectionDim rst1 As ADODB.Recordset, rst2 As ADODB.RecordsetDim stDB As String, stSQL1 As String, stSQL2 As StringDim stConn As StringDim wbBook As WorkbookDim wsSheet1 As WorksheetDim lnField As Long, lnCount As Long[/FONT][FONT=Courier New]'Instantiate the ADO-objects.Set cnt = New ADODB.ConnectionSet rst1 = New ADODB.RecordsetSet rst2 = New ADODB.Recordset[/FONT][FONT=Courier New]Set wbBook = ThisWorkbookSet wsSheet1 = wbBook.Worksheets(1)[/FONT][FONT=Courier New]'Path to the database.stDB = "c:\db1.mdb"[/FONT][FONT=Courier New]'Create the connectionstring.stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _         & "Data Source=" & stDB & ";"         'The 1st raw SQL-statement to be executed.stSQL1 = "SELECT * FROM Production_E1"[/FONT][FONT=Courier New]'The 2nd raw SQL-statement to be executed.stSQL2 = "SELECT * FROM Production_E2"[/FONT][FONT=Courier New]'Clear the worksheet.wsSheet1.Range("A1").CurrentRegion.Clear[/FONT][FONT=Courier New]With cnt.Open (stConn) 'Open the connection..CursorLocation = adUseClient 'Necessary to disconnect the recordset.End With[/FONT][FONT=Courier New]With rst1.Open stSQL1, cnt 'Create the recordset.Set .ActiveConnection = Nothing 'Disconnect the recordset.End With[/FONT][FONT=Courier New]With rst2.Open stSQL2, cnt 'Create the recordset.Set .ActiveConnection = Nothing 'Disconnect the recordset.End With[/FONT][FONT=Courier New]With wsSheet1.Cells(2, 1).CopyFromRecordset rst1 'Copy the 1st recordset..Cells(2, 2).CopyFromRecordset rst2 'Copy the 2nd recordset.End With[/FONT][FONT=Courier New]'Release objects from the memory.rst1.CloseSet rst1 = Nothingrst2.CloseSet rst2 = Nothingcnt.CloseSet cnt = NothingEnd Sub

[/FONT]

Re: Using paste special when using Copy Destination

Great, thanks for sharing guys!

Re: Using paste special when using Copy Destination

I tried in one LINE but it error so try this way too

Re: Using paste special when using Copy Destination

Sub try()
For Each ws In Worksheets
Select Case UCase(ws.Name)
Case "Index", "Data", "Summary", "Charts"
Case Else
ws.Range("Z6:AB11").Copy
ShEEtS("Summary").Range("B" & Rows.Count).End(xlUp).Offset(2, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
x = x + 1
End Select
Next ws
End Sub

Re: VBA for website login

Rob, i didn't get back to this thread and didnt see your reply then.
Thank you so much ....learned alot from it.
Thanks again

Re: Public variable to be available in all workbook.

Thanks for clearing things up for me...
Thank you guys!

Hi, can one Public variable declared in one workbook be available in all workbook?

in book1.xlsm

[/FONT][FONT=Courier New]Option ExplicitPublic Ans As StringSub started()Ans = "Hi"MsgBox AnsEnd Sub[/FONT][FONT=Courier New]

[/FONT]

in book2

[/FONT][FONT=Courier New]Sub secondsMAcro()[/FONT][FONT=Courier New]MsgBox AnsEnd Sub[/FONT][FONT=Courier New]

[/FONT]


It gives blank msgbox why?

I want it to be able to transfer this value from book1 to book...

Please clarify this for me.

Thanks for helping.

Re: Charts problem in Shared Workbook. Run time Err,Method of visible chartobjects fa

Rory, thank you for getting back making things clear....
I thought there could be a way...

Thanks again

I have a chart in SHARED Workbook
I am getting error "Run time Error: Method of visible chartobjects failed.

Is there a way to make the CHART work in SharedBook too?
Current Code

[/FONT][FONT=Courier New]Sub UpdateChart()    Dim ChtObj As ChartObject    Dim UserRow As Long    Set ChtObj = ActiveSheet.ChartObjects(1)    UserRow = ActiveCell.Row    If UserRow < 4 Or IsEmpty(Cells(UserRow, 1)) Then        ChtObj.Visible = False    Else        ChtObj.Chart.SeriesCollection(1).Values = _           Range(Cells(UserRow, 2), Cells(UserRow, 6))        ChtObj.Chart.ChartTitle.Text = Cells(UserRow, 1).Text        ChtObj.Visible = True    End IfEnd Sub

[/FONT]

Re: Counting Found result from find.

Pike, thanks again. It was all new for me. Thank you so much you did it!!! Great!!!

Have a great weekend!

Re: Counting Found result from find.

Thanks alot for replying. THat was great! Can I also mke vba select the last found result and copy it to shee1 range("A1")

Thanks again

Hi, is there a way to count found result from FIND in vba?
I want a msgbox found count.
Suppose if there are 278 words called "Ext1" in finding range i want vba to retuen result in msgbox.

Thanks for helping.

Pedie

Re: Make custom button available to all workbooks

Thanks alot!

Re: Custom Toolbars And Faceids

How can i make the icon look bigger?

Re: Custom Toolbars And Faceids

Thanks.

I need help with excel custom button to be there in addin tab ribbon in all workbooks. The link below from this site helped me build custom button however when i saved this file as addin with xlam format the menu/button that appear in addin does not appear.
Please suggest.

http://www.ozgrid.com/VBA/custom-menus.htm

Thanks
Pedie

Re: How to stop users from running vba into the sheet in book1

Hi Mike, thanks.
it is 'no'. But i cant hide it for all users uses this sheet to update other sheet (sort of form sheet)...I dont know why there is a breaker code my mean concern is this sheet..because i do not want other users to change the information and manupulate the data....I tried locking up everything but still there is a way to break the password...

PS: if i run this code from thisworkbook module as the book opens up will it stop from other macro running in this sheet?

Hi, how to stop users from running vba into the sheet in book1 when it is not thisworkbook macro.
I mean, is there a way to stop users running macro into sheet1 of book1 from any other workbook?

Thanks in advance for helping.

Re: Select Worksheet Based on ComboBox

If you mean the you want the code to select the sheet depending on the combobox value then something like this.

dim x as string
x= ComboBox1.value & "*"


sheets(x).select
'run  your code..

Re: Cut and Paste a column of a Table

Did you try pasting it as paste special values and formulas only?