We had someone (I'm not sure) who make a data extractor in excel for the lab sometime before I joined this save much time when working with large data sets as I am sure you are aware. In the file labeled (Aga Lab Template_Data Extractor (for Xcalibur)) there is a cell that is filed with the text (ExcelExp_Long_Floc_BAC) this can be replaced with the name of your excel file to be extracted, (in testing I have changed the file name to "3" for simplicity) the macro seems to open the file and copy some of the cells from the extracted file to hidden sheets however when it attempted to access the excel file a second time there is an error
I believe this means that it can not access the file but I am unsure why I have done some digging in the code but my VB knowledge is lacking.
the code can be seen below and errors at (Workbooks(wb_data). Activate) in the ('GET SHEET NAMES) section
Attached are the two files that are referenced above.
Sub Generate_Report()
Application.ScreenUpdating = False
Dim wb_main, wbm_path, wbm_title, wbm_ds, wbm_out, _
wb_data, wbd_path As String
wb_main = ActiveWorkbook.Name
wbm_title = "Title"
wbm_ds = "Data Sheet"
wbm_out = "Output"
Workbooks(wb_main).Sheets(wbm_ds).Visible = True
Workbooks(wb_main).Sheets(wbm_out).Visible = True
'CLEAR PREVIOUS DATA
Workbooks(wb_main).Sheets(wbm_ds).Activate
Workbooks(wb_main).Sheets(wbm_ds).Range("B2:B251").ClearContents
Workbooks(wb_main).Sheets(wbm_ds).Range("D2:D251").ClearContents
'OPEN DATA FILE
wbm_path = Workbooks(wb_main).Path
wb_data = Workbooks(wb_main).Sheets(wbm_title).Range("F3")
wbd_path = wbm_path & "\" & wb_data
Workbooks.Open wbd_path
'GET SAMPLE LABELS
Dim num_label As Integer
num_label = Range("A6").End(xlDown).Row
Range("A6:A" & num_label).Select
Selection.Copy
Workbooks(wb_main).Sheets(wbm_ds).Range("B2").PasteSpecial xlPasteValues
'GET SHEET NAMES
Workbooks(wb_data).Activate
Sheets.Add
Dim ws As Worksheet
Dim iRow, iCol, num_sheet As Integer
iRow = Selection.Row
iCol = Selection.Column
For Each ws In Worksheets
Cells(iRow, iCol) = ws.Name
iRow = iRow + 1
Next ws
num_sheet = Range("A2").End(xlDown).Row - 1
Range("A2:A" & num_sheet).Select
Selection.Copy
Workbooks(wb_main).Sheets(wbm_ds).Range("D2").PasteSpecial xlPasteValues
Dim num_sample, num_compound As Integer
Workbooks(wb_main).Sheets(wbm_ds).Activate
num_sample = Workbooks(wb_main).Sheets(wbm_ds).Range("G2").Value
num_compound = Workbooks(wb_main).Sheets(wbm_ds).Range("G3").Value
Workbooks(wb_main).Sheets(wbm_ds).Range("D2:D" & num_compound + 1).Select
Selection.Sort Key1:=Workbooks(wb_main).Sheets(wbm_ds).Range("D2"), Order1:=xlAscending
'COPY PASTE TABLE TO A NEW SHEET
Workbooks(wb_main).Sheets(wbm_out).Activate
Workbooks(wb_main).Sheets(wbm_out).Range(Cells(2, 2), Cells(2 + num_compound, 2 + num_sample)).Select
Selection.Copy
Sheets.Add
Range("A1").PasteSpecial xlPasteValues
Range("A1").PasteSpecial xlPasteFormats
Range("A1").PasteSpecial xlPasteColumnWidths
ActiveSheet.Name = "Sheet1"
ActiveSheet.Move
'CLOSE AND HIDE BOOK/SHEETS
Workbooks(wb_data).Close Saved = True
Workbooks(wb_main).Sheets(wbm_title).Visible = True
Workbooks(wb_main).Sheets(wbm_ds).Visible = xlVeryHidden
Workbooks(wb_main).Sheets(wbm_out).Visible = xlVeryHidden
MsgBox num_sample & " samples; " & num_compound & " compounds"
End Sub
Sub Unhide()
ActiveWorkbook.Sheets("Data Sheet").Visible = True
ActiveWorkbook.Sheets("Output").Visible = True
End Sub
Display More