I'm no expert with excel, but I see a lot of typical posts in the board here.
While this is probably entirely doable from access, I think it's a good idea
to take a step back sometimes and decide who should be doing what. e.g. -
let access to access work, and let excel do excel work. If the format in
excel is able to brought into a table, bring the whole worksheet into a temp
table and work with it from access. If it's a 'visual' data layout not
acceptable as normalized data, write a little function in excel to export the
required data to a file, and then import that data into access.
I think this may be a much more managable approach. Though like I said, I'm
no excel expert, and I'm pretty sure this can be accomplished in access.
hth
--
Jack Leach
www.tristatemachine.com
"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)
"BTU_needs_assistance_43" wrote:
> Below I have the code I'm writting for a database. I've written in
> descriptions of what I think the various lines of code will accomplish. I
> have it currently set up to search for recently modified Excel files in a
> folder on the network, and this part of the code works and works very well.
> It then counts the number of recently modified files and sets up to repeat
> the functions I give it for each of the new files it finds. I've defined the
> table "Cast Reports" as the target table for all the information it drags out
> of the Excel files. What I need my program to do now is to extract all the
> information out of every consecutive cell, moving down one column and
> restarting when it reaches the end of the row, adding the information to my
> database in a mirrored fashion. I need it to do this until it reaches any
> cell with a value of 0, and then end for that Excel file and move onto the
> next Excel file to repeat the process for all values of (i) until its gone
> through all the files it found.
>
> This is what I need to start with, although I'll also ultimately need to be
> able to link the values from this table to a form/report by equal values
> values given in one of the columns (a name column that will be extracted from
> the excell files). I'm kinda outta my programming league on this one so I
> really need the help... Thanks in advance.
>
>
> Private Sub Command5_Click()
>
> Dim blnHasFieldNames As Boolean
> Dim strWorksheet As String, strTable As String
> Dim strPath As String, strPathFile As String
> Dim strCells As String
> Dim dbs As DAO.Database
> Dim rst As DAO.Recordset
>
> ' Change this next line to True if the first row in EXCEL worksheet has
> field names
> blnHasFieldNames = False
>
> Dim strWorksheets As String
> ' Name of the worksheet that is to be imported from each file
> strWorksheets = "Cast"
>
> Dim strTables As String
> ' Name of the table that is to be exported too
> strTables = "Cast Reports"
>
> ' Searches For Recently Modified Excel Files
> With Application.FileSearch
> .NewSearch
> .LookIn = "G:\DLDATA\Pit Volumes\East"
> .SearchSubFolders = True
> .LastModified = msoLastModifiedThisWeek
> .FileType = msoFileTypeExcelWorkbooks
> If .Execute > 0 Then
>
> ' Sets program to repeat as many times as there are files found that
> match search criteria
> For i = 1 To .FoundFiles.Count
>
>
>
> ' Using this object, we open the .xls file
> ' It points at it by defining the workbook as the current file
> that was searched for
> Excel.Workbook objBook = objExcel.Workbooks.Add(".FoundFiles(i)")
>
> 'Declaration of the variables we will use.
> strCells = ""
> chrNextColumn = ("A")
> intNextRow = ("5")
> Dim strNextCellContent As String
> Dim strNextCell As String
>
> Do
> ' This defines the cell that we're going to read.
> ' It's the Column (A) plus the Row (5)
> strNextCell = chrNextColumn + Convert.ToString(intNextRow)
> ' This is the variable where your data will go.
> strCells = objSheet.get_Range(strNextCell,
> System.Reflection.Missing.Value).Value2.ToString() + " "
> ' This makes the next column by making the character 'A' into a
> number, adding 1 to it, and turning it back into a character, returning 'B'
> chrNextColumn = Convert.ToChar(Convert.ToInt32(chrNextColumn) + 1)
> ' This will make the next cell to check.
> strNextCell = chrNextColumn + Convert.ToString(intNextRow)
>
> ' This try command will see if there is anything in the next cell.
> try
> strNextCellContent = objSheet.get_Range(strNextCell,
> System.Reflection.Missing.Value).Value2.ToString()
>
> ' Usually, in the case that there is Nothing in the next cell,
> we get the error "NullReferenceException" but by using this Catch command, we
> instead execute new code.
> catch (System.NullReferenceException)
>
> ' The catch code resets the column letter so that we start at 'A'
> again, and it adds 1 to the row, so now we start on the second row.
> intNextRow = 1
> chrNextColumn = (A)
>
> ' This separates the two rows in the strCells string.
> ' Comment or remove the next line if you want your data all in on
> one line.
> strCells = "\n"
>
> ' Now we reset the next cell to check
> strNextCell = chrNextColumn + Convert.ToString(intNextRow)
>
> ' And we check if there is anything in it again.
> try
> strNextCellContent = objSheet.get_Range(strNextCell,
> System.Reflection.Missing.Value).Value2.ToString()
>
> ' If we catch it again, that means there is no more data left
> in the whole Excel sheet, and we create the conditions to exit this While
> loop.
> catch (System.NullReferenceException)
>
> strNextCellContent = ""
>
> While (strNextCellContent = "")
>
> ' Now I outputted the string in a MessageBox. You can do whatever
> you want with it.
> MessageBox.Show (strCells)
>
> intWorksheets
>
> strFile = Dir(strPath & "*.xls")
> Do While Len(strFile) > 0
> strPathFile = strPath & strFile
> DoCmd.TransferSpreadsheet acImport, _
> acSpreadsheetTypeExcel9, strTables(intWorksheets), _
> strPathFile, blnHasFieldNames, _
> strWorksheets(intWorksheets) & "$"
> strFile = Dir()
> Loop
>
>
>
>
>
>
>
>
> Next i
> Else
> MsgBox "There were no recent Excel files found."
> End If
> End With
>
> End Sub
>
Similar Threads
