Excel cell value into Access VBA variable

[Timoo]

Hi,

Right now I am setting up a database to import 'random' Excel files, placed in a certain folder.

These files are set up as follows:

• customer can fill in a certain table (unlocked cells in a locked sheet/workbook).
• second (invisible) sheet copies values from customer sheet, from row 1 to 228, so I have a neat table to import
• Access will read the file, import the invisible sheet, job well done.

There is only one problem: Access imports all 228 rows, where I only want the filled-in rows.
So, I made a counter, counting all empty cells and subtracting that from 228.
This gives me a value for "filled-in lines".
This value I want to store into a variable, so I can import cells (A1:E<counter>)
Now, how do I read a certain cell into a VBA variable?

Currently used code to import (working):

    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferSpreadsheet acImport, , _
        "DealerLists", strPath & strFileList(intFile), True, "parts!A1:E229"
    Next

If my counter value is located in sheet("counters"), field(A2), how do I replace the (!A1:E229) by (A1:E<counter>)?

Complete formula:

Sub Link_To_Excel()
     'WillR - [URL="http://www.willr.info"]www.willr.info[/URL] (December 2004)
     'Macro Loops through the specified directory (strPath)
     'and links ALL Excel files as linked tables in the Access
     'Database.
     
    Const strPath As String = "xxfilelocation" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
     
     'Loop through the folder & build file list
    strFile = Dir(strPath & "*.xlsx")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     
     'cycle through the list of files & import to Access
     'appending to tables called DealerLists and DealerContacts
    For intFile = 1 To UBound(strFileList)
        "DealerLists", strPath & strFileList(intFile), True, "parts!A1:E229"
        DoCmd.TransferSpreadsheet acImport, , _
        "DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
    Next
    MsgBox UBound(strFileList) & " Files were Imported"
End Sub

Thanks in advance for your help!
Timoo

 

[SpentGeezer]

'Greeting.

'Firstly get the counter value
Set objApp = CreateObject("Excel.Application")
objApp.Visible = False
Set wb = objApp.Workbooks.Open("fileHere.xls", True, False)
Counter = wb.Sheets(1).Rows(1).value
wb.quit ' or whatever you use to c lose excel..my brain hurts today so look it up
set objApp = nothing

For intFile = 1 To UBound(strFileList)
"DealerLists", strPath & strFileList(intFile), True, "parts!A1:E" & counter
DoCmd.TransferSpreadsheet acImport, , _
"DealerContacts", strPath & strFileList(intFile), True, "contact!A1:F2"
Next


'there you go bro.....the spent one has provided his geezer

 

[Timoo]

Wow SpentGeezer,

*Thát sounds easy*

Merci Beaucoup, going to test it immediately