Posts by CosmicPotato
-
-
-
-
-
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.
Code
Display MoreSub 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