ads_3131
04-03-2012, 06:24 AM
Hello
Really need help with a problem i have with an exported sheet.... My exported worksheet goes into a new workbook (from the host sheet) but my problem is ... it creates external links to the original workbook.
I know its probably the way that my VBA copys and exports , however its the only way i can bypass the 255 Character issue with exporting.
I need help with either removing the external links while exporting / or copying a module across from the host sheet into the exported sheet to then run upon startup....? any ideas, any help would be great thanks.....
my code so far is :
------------------------------------------------------------------------------
Sub CopyAll()
Dim wks As Worksheet
Dim wbNew As Workbook
Dim fName As String
Dim lngConn As Long
On Error Resume Next
Set wbNew = Workbooks.Add(xlWBATWorksheet)
With ThisWorkbook.ActiveSheet
.Copy Before:=wbNew.Worksheets(1)
Application.DisplayAlerts = False
wbNew.Worksheets(2).Delete
Application.DisplayAlerts = True
fName = ActiveSheet.Name
ChDir "C:\Users\Administrator\Desktop"
' issued with the directory path depending on the operating system - etc win7 ,xp etc etc
ActiveWorkbook.SaveAs Filename:=fName & " " & Format$(Date, "mm-dd-yyyy"), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
wbNew.Close False
Really need help with a problem i have with an exported sheet.... My exported worksheet goes into a new workbook (from the host sheet) but my problem is ... it creates external links to the original workbook.
I know its probably the way that my VBA copys and exports , however its the only way i can bypass the 255 Character issue with exporting.
I need help with either removing the external links while exporting / or copying a module across from the host sheet into the exported sheet to then run upon startup....? any ideas, any help would be great thanks.....
my code so far is :
------------------------------------------------------------------------------
Sub CopyAll()
Dim wks As Worksheet
Dim wbNew As Workbook
Dim fName As String
Dim lngConn As Long
On Error Resume Next
Set wbNew = Workbooks.Add(xlWBATWorksheet)
With ThisWorkbook.ActiveSheet
.Copy Before:=wbNew.Worksheets(1)
Application.DisplayAlerts = False
wbNew.Worksheets(2).Delete
Application.DisplayAlerts = True
fName = ActiveSheet.Name
ChDir "C:\Users\Administrator\Desktop"
' issued with the directory path depending on the operating system - etc win7 ,xp etc etc
ActiveWorkbook.SaveAs Filename:=fName & " " & Format$(Date, "mm-dd-yyyy"), _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
wbNew.Close False