I have an Excel file that I am trying to upload into MS Access, I can easily click on a subform (which allows me to call the SampleID) then find that SampleID on the Excel Spreadsheet in column A; which will in-put other columns in that same row.
However now I want to use a whole query's SampleID to check to see if any of them are in column A of a spreadsheet; if they are in-put data (easy vba codes for in-putting).
Would I use two loops? one to go through each sample of the query then through each sample of the excel files? Is there any other way to go about this? I havent gotten the two loops to work... which is why I am trying to see what would be the best way to do this.
Excel SpreadSheet
Query
This code might be hard to follow (copied from different websites and tried to combine it to make it work (not to much of a VBA coder myself but trying to understand it more as I keep trying)).
The code pretty much uploads a CSV/XLSX file; figures out the number of rows, and starts a loop (i) until it looks through all the number of rows.
Then the code will look at a textbox that has the total number of rows in the query; this will start a loop
until it reads all the query's sample names.
Lastly if the names match in both then print out the name (normally I would in-put data but I just wanted to see if it would even print out the matching name which it doesnt (Not even one which is what I was expecting)).
However now I want to use a whole query's SampleID to check to see if any of them are in column A of a spreadsheet; if they are in-put data (easy vba codes for in-putting).
Would I use two loops? one to go through each sample of the query then through each sample of the excel files? Is there any other way to go about this? I havent gotten the two loops to work... which is why I am trying to see what would be the best way to do this.
Excel SpreadSheet
Code:
[B]Sample.ID Data
[/B]Sample1 1.04142
Sample2 2.44058
Sample4 4.15872
Code:
[B]Sample.ID Data
[/B]Sample1 0
Sample2 0
Sample3 0
Sample4 0
Code:
Private Sub Command73_Click()
Dim fd As FileDialog
Dim NormalizedFileName As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Add "Comma Delimited Files", "*.csv", 1
.InitialFileName = "//Wrenlab02/research files/MULTIPLE MYELOMA/PCR DATA/MM Screen PCR/" & Year(Date) & "/"
.InitialView = msoFileDialogViewDetails
.AllowMultiSelect = False
.Title = "Select CT Values to be imported for your Multiple-Myeloma Sample"
If .Show Then
selectFile = .SelectedItems(1)
Dim xl As Object
Dim xlWrkBk As Object
Dim xlSht As Object
Set xl = CreateObject("Excel.Application")
xl.Visible = False 'Control whether or not Excel should be visible to
'the user or not.
Set xlWrkBk = xl.Workbooks.Open(selectFile)
Set xlSht = xlWrkBk.Worksheets(1)
Dim i As Integer
Dim n As Integer
n = 1
i = 2
LastRow = xlSht.UsedRange.Rows.Count
Do While (i < LastRow)
SampleName = xlSht.Cells(i, 1)
Do While (n < [subformtblFacilityPatients].Form![Total])
If SampleName = [subformtblFacilityPatients].Form![WrenID] Then
Me.Text74 = [subformtblFacilityPatients].Form![WrenID]
End If
n = n + 1
Loop
i = i + 1
Loop
xlWrkBk.Close False 'Close the Workbook without saving any changes
xl.Quit 'Close Excel
Else
End
End If
End With
Set xlSht = Nothing
Set xlWrkBk = Nothing
Set xl = Nothing
Set fd = Nothing
End SubThis code might be hard to follow (copied from different websites and tried to combine it to make it work (not to much of a VBA coder myself but trying to understand it more as I keep trying)).
The code pretty much uploads a CSV/XLSX file; figures out the number of rows, and starts a loop (i) until it looks through all the number of rows.
Then the code will look at a textbox that has the total number of rows in the query; this will start a loop
until it reads all the query's sample names. Lastly if the names match in both then print out the name (normally I would in-put data but I just wanted to see if it would even print out the matching name which it doesnt (Not even one which is what I was expecting)).
