Hey crew,
I have this code that saves the newly created worksheet (from Code that imports text to excel) as a new workbook:
After this piece of code run (from a form), it goes back to the workbook in which the code resides and do other stuff (clears the sheet that just got converted to a brand new workbook, clears connections and querytables, etc, etc.).![]()
Private Sub SaveWS() Dim wb As Workbook If Application.Version > 11 Then ActiveWorkbook.Worksheets(cboRptname.Value).Copy Set wb = ActiveWorkbook wb.SaveAs Filename:="\\Server1\Folder1\Folder2\Folder3\" & BoxYrFolder.Value & "\" & BoxQtr.Value & "\Cart\" & _ BoxMonthText.Value & "\" & cboRptname.Value & " " & cboYear.Value & cboMonth.Value & cboDay.Value & ".xls", _ FileFormat:=xlExcel8 Else ActiveWorkbook.Worksheets(cboRptname.Value).Copy Set wb = ActiveWorkbook wb.SaveAs Filename:="\\Server1\Folder1\Folder2\Folder3\" & BoxYrFolder.Value & "\" & BoxQtr.Value & "\Cart\" & _ BoxMonthText.Value & "\" & cboRptname.Value & " " & cboYear.Value & cboMonth.Value & cboDay.Value & ".xls", _ FileFormat:=xlWorkbookNormal End If ActiveWorkbook.Close End Sub
After all the above is done, I would like to go to
And add a hyper link to the file just created and saved by the code above. And I want the text to be displayed "cboRptname.Value & " " & cboYear.Value & cboMonth.Value & cboDay.Value & ".xls"![]()
Workbooks(ThisWorkbook.Name).Worksheets("Generator").Range("A7")
Can anybody help?
EDIT: I tried the macro recorder, but I use xl07 and I want users on xl03 to use the file as well, and I'm always concerned with the way xl07 records code that it may not work on earlier versions.











LinkBack URL
About LinkBacks


Register To Reply
Bookmarks