hope someone can help with excel save with 2003 and 2007 installed on my computer

  • Thread starter Thread starter sparks
  • Start date Start date
S

sparks

This works but I am having a lot of problems with the output.
the input file has 470 variables in a csv file so I can't use 2003.
but the only thing I can get is an xls file with the first 255
variables ....

I thought maybe a direct path to the 2007 install would help but it is
still saving as a 2003 output xls file
changing the extension to xlsx only makes the save not open becasue of
incorrect naming.

how can I make this save as a full xlsx file with all of the
variables?

thanks for any help I am stumped.

NOT SURE ABOUT THE PATH
it runs but like I said I only get xls type of output so I don't know
if 2003 or 2007 is running...how do I tell?



Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object

oXLPath = """C:\Program Files\Microsoft Office\Office12"" " &
"/automation -Embedding"

Set oExcel = CreateObject("Excel.Application")

Set oBook = oExcel.Workbooks.Open("C:\20pagetest.csv")
Debug.Print xlCurrentPlatformText

oBook.SaveAs "c:\booktest.xlsx", xlWorkbookNormal

oExcel.Quit
 
Hi. The code below should be input in Thisworkbook object rather than
a seperate macro.. You can also force a name as well..

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.EnableEvents = False
filesavename = Application.GetSaveAsFilename(fileFilter:="Microsoft
Excel File (*.xlsx), *.xlsx")
If filesavename <> False Then
On Error Resume Next
Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=filesavename
Application.DisplayAlerts = True
On Error GoTo 0
End If
Application.EnableEvents = True
Cancel = True
End Sub
 
sparks submitted this idea :
This works but I am having a lot of problems with the output.
the input file has 470 variables in a csv file so I can't use 2003.
Click to expand...

Why not? How ae you importing this?
 
Sorry I was not clear. I was having a very bad day yesterday.
I can running this in Access so excel is running in the background and
a prompt wiill not work.

it works but it will only write an xls file meaning that it will only
save 255 variables and I have 447 so I need a 2007 save.





Private Sub Command40_Click()

Dim oExcel As Object
Dim oBook As Object
Dim oSheet As Object
'oXLPath = """C:\Program Files\Microsoft Office\Office12"" " &
"/automation -Embedding"
'should not be needed if correct paths are set
Set oExcel = CreateObject("Excel.Application")

Set oBook = oExcel.Workbooks.Open("C:\20pagetest.csv")
Debug.Print xlCurrentPlatformText

oBook.SaveAs "c:\booktest.xlsx", xlWorkbookNormal

oExcel.Quit

End Sub
 
sparks was thinking very hard :
it runs but like I said I only get xls type of output so I don't know
if 2003 or 2007 is running...how do I tell?
Click to expand...
Set oExcel = CreateObject("Excel.Application")
Click to expand...

If oExcel.Version < 12 Then '//it's 2003 or earlier
'process as early
Else '//it's 2007 or later
'process as latest
End If

Why do you need the path? The OS knows which Excel version to run!
 
sparks was thinking very hard :
it works but it will only write an xls file meaning that it will only
save 255 variables and I have 447 so I need a 2007 save.
Click to expand...

Exactly what do you mean by "variables"?

A variable in the context of this forum is a placeholder for data in a
VBA project. If your project has that many variables then you need to
seriously redesign it regardless if it saves or not in early versions.

I suspect, though, that you mean something entirely different like the
character length of cell contents or some such...
 
my system has 2003 and 2007 installed on it.
everything I do defaults to 2003 for some reason.
I can go to the office12 directory and run 2007 fine.
even the open with wiki in xp I specify office 2007 and it will open
the file with 2003 version
 
my system has 2003 and 2007 installed on it.
everything I do defaults to 2003 for some reason.
I can go to the office12 directory and run 2007 fine.
even the open with wiki in xp I specify office 2007 and it will open
the file with 2003 version







- Show quoted text -
Click to expand...

Well then do the following to default to have an xls file default to
excel 2007. in explorer go to tools > folder option > file types.
find the xls extension in the file types list then click change then
browse to the excel version you need. make sure "Always use the
selected program.. is selected...
 
sparks formulated the question :
my system has 2003 and 2007 installed on it.
everything I do defaults to 2003 for some reason.
I can go to the office12 directory and run 2007 fine.
even the open with wiki in xp I specify office 2007 and it will open
the file with 2003 version
Click to expand...

Unfortunately, AND very annoyingly, Live Update will change your
default MSO apps according to what updates it downloads and installs. I
have XL2000, XL2002, XL2003, and XL2007 installed on my dev machine. MS
only updates for 2003 onward now and so if there's no 2007 updates my
default becomes XL2003 after installing updates for that version.

The easiest way I've found to get XL2007 back as my default is to do a
'Repair' in ControlPanel/AddRemovePrograms.
 
Back
Top