Posts by Susy

Re: vba, Retrieving data from an Access Database into Excel

Thanks AlanSidman, I guess I forgot them even though I am pretty sure I used them...

Hi there,

how can i catch this error before it happens, i.e., when OpenDatabase(DBFullName) is nothing... but I have tried with If is nothing... or if len()<= 0 then .... it doesn't work

Code:

Dim db as dao.DatabaseDim DBFullName as StringDBFullName = wkmain.(Cells(30,3).Value ' ---> let's assume that the database in this path cannot be found, then what? --> I would like to check before "Set db = Opendatabase()" if is not empty.Set db = OpenDatabase(DBFullName)

Do you have any ideas how to check before running into error 3024?

Thanks in advance!

Hi there,

would anyone out there be so kind to explain me what the "Case 2, 4, 6, 34" means?

I found this code on the internet and I am trying to do something similar... but I would like to know what I am doing

Sub Fle_FileSearch_Fldrs(sPath As String, _
    sFleKey As String, lN As Long, vFleLst() As Variant, _
    Optional bSbFldr As Boolean = False)




Dim oFso As Object, oFolder As Object, oFile As Object




    Set oFso = CreateObject("Scripting.FileSystemObject")




    If lN = 0 Then
        lN = 1 + lN
        ReDim Preserve vFleLst(1 To 2, 1 To lN)
        vFleLst(1, lN) = "Files Found - Path"
        vFleLst(2, lN) = "Files Found - Name"
    End If




    For Each oFile In oFso.GetFolder(sPath).Files
        Select Case oFile.Attributes
        Case 2, 4, 6, 34    
        Case Else
            If (Not oFile.Name Like "~$*") * _
                (oFile.Path & "\" & oFile.Name <> ThisWorkbook.FullName) * _
                (UCase(oFile.Name) Like UCase(sFleKey)) Then




                lN = lN + 1
                ReDim Preserve vFleLst(1 To 2, 1 To lN)
                vFleLst(1, lN) = sPath
                vFleLst(2, lN) = oFile.Name




    End If: End Select: Next




    If bSbFldr Then
        For Each oFolder In oFso.GetFolder(sPath).subfolders
            Call Fle_FileSearch_Fldrs(oFolder.Path, sFleKey, lN, vFleLst, bSbFldr)
    Next: End If




End Sub

Please let me know!

Re: excel vba --- from one workbook get data from n workbooks and retrieve data in sh

Hi Carim,

thanks... I am going to take a look.

Have a nice day.

hi everyone,

I believe this has been done already... unfortunately I cannot find it!

I need to get data from one Worksheets("Data") which is saved in various workbooks (sometimes more than 50 workbooks in one folder).

Instead of opening each workbook and going to the SheetName where the data is saved, I would like to create a macro which:
1. get the data from this SheetName from all Workbooks without "opening" all the workbooks.
1.1 from Main Workbook (where macro is saved) I need to open the workbooks
1.2 once the workbook is open, get the data in WorksheetName.Cells(X,X).Copy
1.3 Close the workbook (external workbook) and go back to Main Workbook.
1.4 in Main Workbook in SheetDataCollection saved the data from workbooks row wise.

2. Do the same above for the next Workbook in the specified folder...
2.1 Do this until the last file (Workbook).

Can anyone help me with these?

Cheers!

Re: vba search function for strings and make suggestions based on a list!

Thanks Carim,

how were you able to read the data from the table (ms access) with the correct entries...

The code you proposed uses the tablearray to read the entries in excel... just very curious

Regards

Re: vba search function for strings and make suggestions based on a list!

Thanks, but that won't work... vba code is expected unfortunately.

I know this is a hard one.

Hi there,

has anyone stumbled upon some interesting code (Function) to search for strings in a table and make suggestions?

Below an image of what I would like to implement... of course the list contains hundreds of correct names

[ATTACH=CONFIG]71592[/ATTACH]

I would appreciate if you could give me a hint!

Regards

Re: vba filename search for &quot;.&quot; within the name for csv

Hi Trebor76,

I was not able to use your code 1 to 1, but your approach helped me solve my problem...

From your code I used the GetExtensionName Method and

InStr(strMyFile, ".") > 0 ThenExit Sub

code.

Thanks for your time and insight!

Greetings!

Hi there,

I need some help... I am trying to get the filename of a file, in some folder, which contains "." (periods) in its name.

All the files are "csv".

For example: myfile.03.01.2017.csv

--> Filename: myfile.03.01.2017

So, once I get the filename as above, I need to find out if the filename has a "." (period).

It it has a "." then exit sub, otherwise continue with the sub procedure...

Unfortunately, I have no idea how to do this.

Any idea?

Cheers

Re: vba excel combobox loading values from query ms access

This is the final version... I tested it and it works.... it was really simple... Enjoy!
:0ops:

However, this is ADO and DAO together!!!
http://www.fontstuff.com/vba/vbatut10.htm

With ComboBox1
          Do
              .AddItem rs![First_Name]
              rs.MoveNext
          Loop Until rs.EOF        
      End With

Sub ComboBox1_DropButtonClick()




Const Pathname As String = "C:\Users\Susy\Desktop\test.accdb" 
Dim db As DAO.Database 
Dim rs As DAO.Recordset 
Dim rCount As Long 
Dim SQL As String 
Dim LastNames as Variant




Set db = OpenDatabase(Pathname) 
 
SQL = "SELECT DISTINCT First_Name " 
SQL = SQL & "FROM Names " 
 
Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)


If rs.RecordCount = 0 Then 
    MsgBox "Please Select a First Name"     
Else 
     With ComboBox1
          Do
              .AddItem rs![First_Name]
              rs.MoveNext
          Loop Until rs.EOF        
     End With
End If




End Sub

Re: vba excel combobox loading values from query ms access

It doesn't work. I really don't get it. UBound(FirstNames) or UBound(LastNames) is 0. From the beginning the code did not want to read the first recordset.

At the end, all for nothing.

Re: vba excel combobox loading values from query ms access

Sorry, Fdata was just a test... I am going to try with your code...Thanks again!

Re: vba excel combobox loading values from query ms access

Sorry, Fdata was just a test... I am going to try with your code...Thanks again!

Re: vba excel combobox loading values from query ms access

It seemed good, almost too good. :hammerhe:

Unfortunately, the code has a big bug!!!!

These lines

For i = 0 To UBound(FData)
                    .List(.ListCount - 1, i) = rs.Fields(i)
                Next
                End With
                rs.MoveNext
            Loop

are forgetting the one value from this line....

ComboBox2.Value = FData(0, 0)

So at the end, if there are more than one recordset, then I get all of them minus 1 (n-1)... which is not good at all!!!!

If anyone has an idea how to fix this, I will be really thankful!

Thanks again.

Re: vba excel combobox loading values from query ms access

You are welcome KjBox.

This way we can all learn!

:dance:

Re: vba excel combobox loading values from query ms access

Finally........ it is not pretty but it works, for now :music:

Thank you all!

If rs.RecordCount = 0 Then
        MsgBox "Please Select a First Name" 
        GoTo SubExit


    Else


        LastNames = rs.GetRows()


        ComboBox2.Clear


        If rs.RecordCount = 1 Then
    
            ComboBox2.Value = LastNames(0, 0)
        Else
    
            Do Until rs.EOF
                With ComboBox2
                .AddItem
                For i = 0 To UBound(LastNames)
                    .List(.ListCount - 1, i) = rs.Fields(i)
                Next
                End With
                rs.MoveNext
            Loop
        End If
    End If

Re: vba excel combobox loading values from query ms access

Hi KjBox,

I could do that; however, I would rather do it separately... I am still trying to understand why the line of code below is not working...

.List(.ListCount - 1, i) = rs.Fields(i)

Re: vba excel combobox loading values from query ms access

Hi KjBox,

I could do that; however, I would rather do it separately... I am still trying to understand why the line of code below is not working... :smash:

.List(.ListCount - 1, i) = rs.Fields(i)

Re: vba excel combobox loading values from query ms access

When change the "Do Until rs.EOF" for this:

Do While not rs.EOF

the code runs and stops here...

.List(.ListCount - 1, i) = rs.Fields(i)

This is really weird.