B
BTU_needs_assistance_43
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
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