R
RSnyder
I have VB code to basically wipe out old data in a reporting workbook, open up new sources of data from either an Excel file that is pre-loaded or a text file, and then copy the new data to my reporting workbook. Sounds easy and I think my code is correct with exception of the fact that it hangs up after opening the 1st file (Excel). The oddity is that the code executes fine as long as I step through it or run it within the MS VB editor. I've included the 1st section to keep it short.
I use a lot of range finding to find variating range sizes in my imported data sets so I know it looks somewhat choppy but I'll clean that up later. My biggest concern is in keeping the code flowing once it opens up the workbook "F:\ACCTG\ACCTGEN\Monarch\RptFiles\GL_404DTL.XLS". Would appreciate any comments and wondering if it might require some type of PUBLIC dimensioning for the code to work on all open workbooks. Thank you
Sub DownloadGLData(
' DownloadGLData Macro updates the the ACTUAL, BUDGET, and PYACTUA
' data in the sheets with the current GEAC data on file
' Macro recorded 10/30/2003 by RSnyde
' Keyboard Shortcut: Ctrl+Shift+
Let BlankData = "
With Applicatio
.Calculation = xlManua
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals
' Clear previous data in data download sheets
' For the GLActivity data sheet
Application.Goto Reference:="FLD_STARTDATA_GLACTIVITY
Range("FLD_STARTDATA_GLACTIVITY").Offset(3, 0).Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.EntireRow.Delet
Range("FLD_STARTDATA_GLACTIVITY").Offset(1, 0).Selec
' For the ACTUAL data sheet
Application.Goto Reference:="FLD_STARTDATA_ACTUAL
Range("FLD_STARTDATA_ACTUAL").Offset(3, 0).Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.EntireRow.Delet
Range("FLD_STARTDATA_ACTUAL").Offset(1, 0).Selec
' For the BUDGET data sheet
Application.Goto Reference:="FLD_STARTDATA_BUDGET
Range("FLD_STARTDATA_BUDGET").Offset(3, 0).Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.EntireRow.Delet
Range("FLD_STARTDATA_BUDGET").Offset(1, 0).Selec
' For the PYACTUAL data sheet
Application.Goto Reference:="FLD_STARTDATA_PYACTUAL
Range("FLD_STARTDATA_PYACTUAL").Offset(3, 1).Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.EntireRow.Delet
Range("FLD_STARTDATA_PYACTUAL").Offset(1, 0).Selec
' ==============================================================
' Open current period General Ledger download files for Corps 404 & 60
' and transfer data
' Open Corp-404/99605 Monarch's parsed Excel file
Workbooks.Open Filename:="F:\ACCTG\ACCTGEN\Monarch\RptFiles\GL_404DTL.XLS
' Select download data to copy
Range("A2").Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.Cop
' Import data to Variance Report sheet
Windows("VarRpt-Curr.xls").Activat
Application.Goto Reference:="FLD_STARTDATA_GLACTIVITY
Selection.Offset(1, 0).Selec
Selection.PasteSpecial Paste:=xlPasteValue
' Close data download file
Windows("GL_404DTL.XLS").Activat
Application.CutCopyMode = Fals
ActiveWorkbook.Close (SaveChanges = False
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' Open Corp-605 Monarch's parsed Excel file
Workbooks.Open Filename:="F:\ACCTG\ACCTGEN\Monarch\RptFiles\GL_605DTL.XLS
' Select download data to copy
Range("A2").Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.Cop
' Import data to Variance Report sheet
Windows("VarRpt-Curr.xls").Activat
Application.Goto Reference:="FLD_STARTDATA_GLACTIVITY
Range("FLD_STARTDATA_GLACTIVITY").End(xlDown).Offset(1, 0).Selec
Selection.PasteSpecial Paste:=xlPasteValue
' Close data download file
Windows("GL_605DTL.XLS").Activat
Application.CutCopyMode = Fals
ActiveWorkbook.Close (SaveChanges = False
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' VarRpt-Curr post transfer processing steps
' Copy formulas for data lines!
Windows("VarRpt-Curr.xls").Activate
Application.Goto Reference:="FLD_STARTFORMULAS_GLACTIVITY"
Range(Selection, ActiveCell.End(xlToRight).Offset(0, 1)).Select
Selection.Copy
Range("FLD_STARTDATA_GLACTIVITY").Offset(1, 13).Select
Range(Selection, ActiveCell.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
' Set cursor to left-top most cell of data in worksheet!
Range("FLD_STARTDATA_GLACTIVITY").Offset(1, 0).Select
' ----------------------------------------------------------------
'
I use a lot of range finding to find variating range sizes in my imported data sets so I know it looks somewhat choppy but I'll clean that up later. My biggest concern is in keeping the code flowing once it opens up the workbook "F:\ACCTG\ACCTGEN\Monarch\RptFiles\GL_404DTL.XLS". Would appreciate any comments and wondering if it might require some type of PUBLIC dimensioning for the code to work on all open workbooks. Thank you
Sub DownloadGLData(
' DownloadGLData Macro updates the the ACTUAL, BUDGET, and PYACTUA
' data in the sheets with the current GEAC data on file
' Macro recorded 10/30/2003 by RSnyde
' Keyboard Shortcut: Ctrl+Shift+
Let BlankData = "
With Applicatio
.Calculation = xlManua
.MaxChange = 0.00
End Wit
ActiveWorkbook.PrecisionAsDisplayed = Fals
' Clear previous data in data download sheets
' For the GLActivity data sheet
Application.Goto Reference:="FLD_STARTDATA_GLACTIVITY
Range("FLD_STARTDATA_GLACTIVITY").Offset(3, 0).Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.EntireRow.Delet
Range("FLD_STARTDATA_GLACTIVITY").Offset(1, 0).Selec
' For the ACTUAL data sheet
Application.Goto Reference:="FLD_STARTDATA_ACTUAL
Range("FLD_STARTDATA_ACTUAL").Offset(3, 0).Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.EntireRow.Delet
Range("FLD_STARTDATA_ACTUAL").Offset(1, 0).Selec
' For the BUDGET data sheet
Application.Goto Reference:="FLD_STARTDATA_BUDGET
Range("FLD_STARTDATA_BUDGET").Offset(3, 0).Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.EntireRow.Delet
Range("FLD_STARTDATA_BUDGET").Offset(1, 0).Selec
' For the PYACTUAL data sheet
Application.Goto Reference:="FLD_STARTDATA_PYACTUAL
Range("FLD_STARTDATA_PYACTUAL").Offset(3, 1).Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.EntireRow.Delet
Range("FLD_STARTDATA_PYACTUAL").Offset(1, 0).Selec
' ==============================================================
' Open current period General Ledger download files for Corps 404 & 60
' and transfer data
' Open Corp-404/99605 Monarch's parsed Excel file
Workbooks.Open Filename:="F:\ACCTG\ACCTGEN\Monarch\RptFiles\GL_404DTL.XLS
' Select download data to copy
Range("A2").Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.Cop
' Import data to Variance Report sheet
Windows("VarRpt-Curr.xls").Activat
Application.Goto Reference:="FLD_STARTDATA_GLACTIVITY
Selection.Offset(1, 0).Selec
Selection.PasteSpecial Paste:=xlPasteValue
' Close data download file
Windows("GL_404DTL.XLS").Activat
Application.CutCopyMode = Fals
ActiveWorkbook.Close (SaveChanges = False
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' Open Corp-605 Monarch's parsed Excel file
Workbooks.Open Filename:="F:\ACCTG\ACCTGEN\Monarch\RptFiles\GL_605DTL.XLS
' Select download data to copy
Range("A2").Selec
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Selec
Selection.Cop
' Import data to Variance Report sheet
Windows("VarRpt-Curr.xls").Activat
Application.Goto Reference:="FLD_STARTDATA_GLACTIVITY
Range("FLD_STARTDATA_GLACTIVITY").End(xlDown).Offset(1, 0).Selec
Selection.PasteSpecial Paste:=xlPasteValue
' Close data download file
Windows("GL_605DTL.XLS").Activat
Application.CutCopyMode = Fals
ActiveWorkbook.Close (SaveChanges = False
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
' VarRpt-Curr post transfer processing steps
' Copy formulas for data lines!
Windows("VarRpt-Curr.xls").Activate
Application.Goto Reference:="FLD_STARTFORMULAS_GLACTIVITY"
Range(Selection, ActiveCell.End(xlToRight).Offset(0, 1)).Select
Selection.Copy
Range("FLD_STARTDATA_GLACTIVITY").Offset(1, 13).Select
Range(Selection, ActiveCell.End(xlDown)).Select
Selection.PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
' Set cursor to left-top most cell of data in worksheet!
Range("FLD_STARTDATA_GLACTIVITY").Offset(1, 0).Select
' ----------------------------------------------------------------
'