mrsmith51
12-15-2010, 08:43 AM
Hello. I need help with something I'm not experienced with in VBA, and have got my botched code in a huge tangle trying to solve.
I need to check whether a specific workbook exists, (create it if it doesn't exist), write a few pieces of data to it, save and close. The workbook will be within a folder of roughly the same name.
The file structure is like this:
\company name\company name week 41.xlsx
Company name is determined by a combobox, week number is determined with a simple formula to calculate the week number of the current date. As each company doesn't visit each week, they won't have a workbook for each week - just for as and when they visit (ex. if they visit on week 40, week 44, week 50, they have workbooks for those weeks, not for the weeks they don't show). This is why it needs to check if the workbook is present and create it first. The folder should already exist for the company, so there is no necessity to check if the folder exists, but I do anyway.
I'll paste my code below
Dim myWorkbook As Workbook
Dim wk As String
Application.DisplayAlerts = False
wk = "C:\Users\Dave\Documents\xl\" & CustomerName & "\" & CustomerName & " Week " & WeekNumber & ".xlsx"
'CREATE THE FOLDER
On Error Resume Next
MkDir "C:\Users\Dave\Documents\xl\" & CustomerName
On Error GoTo 0
'Create a new workbook from the template with the week layout
Set myWorkbook = Workbooks.Add(Template:="C:\Users\Dave\Documents\xl\weektemplate.xltx")
myWorkbook.Sheets("sheet1").Range("A5") = "Week " & WeekNumber
myWorkbook.Sheets("sheet1").Range("A6").Select
'Write all the pieces of data into the new week!
PasteDataInCustomerSheet
'Insert a new row underneat when finished, complete with all formulas
InsertRowsAndFillFormulas
'SAVE THE WORKBOOK
Workbooks(wk).Save
Workbooks(wk).Close
What extension should I be using? I settled on .xlsx after trying .xls which didn't work.
I'd appreciate any guidance or help on this, I've done a lot of googling and right now it just seems like a bodge-job I have on my hands. TIA
I need to check whether a specific workbook exists, (create it if it doesn't exist), write a few pieces of data to it, save and close. The workbook will be within a folder of roughly the same name.
The file structure is like this:
\company name\company name week 41.xlsx
Company name is determined by a combobox, week number is determined with a simple formula to calculate the week number of the current date. As each company doesn't visit each week, they won't have a workbook for each week - just for as and when they visit (ex. if they visit on week 40, week 44, week 50, they have workbooks for those weeks, not for the weeks they don't show). This is why it needs to check if the workbook is present and create it first. The folder should already exist for the company, so there is no necessity to check if the folder exists, but I do anyway.
I'll paste my code below
Dim myWorkbook As Workbook
Dim wk As String
Application.DisplayAlerts = False
wk = "C:\Users\Dave\Documents\xl\" & CustomerName & "\" & CustomerName & " Week " & WeekNumber & ".xlsx"
'CREATE THE FOLDER
On Error Resume Next
MkDir "C:\Users\Dave\Documents\xl\" & CustomerName
On Error GoTo 0
'Create a new workbook from the template with the week layout
Set myWorkbook = Workbooks.Add(Template:="C:\Users\Dave\Documents\xl\weektemplate.xltx")
myWorkbook.Sheets("sheet1").Range("A5") = "Week " & WeekNumber
myWorkbook.Sheets("sheet1").Range("A6").Select
'Write all the pieces of data into the new week!
PasteDataInCustomerSheet
'Insert a new row underneat when finished, complete with all formulas
InsertRowsAndFillFormulas
'SAVE THE WORKBOOK
Workbooks(wk).Save
Workbooks(wk).Close
What extension should I be using? I settled on .xlsx after trying .xls which didn't work.
I'd appreciate any guidance or help on this, I've done a lot of googling and right now it just seems like a bodge-job I have on my hands. TIA