Updating Older Excel Macro That No Longer Runs

[JoelTXCive]

Our office has an older Excel VBA macro that has lost functionality.

The macro had full functionality in Excel 2007, then partial functionality in later versions. The macro has now completely stopped working in Excel 2013.

We no longer have access to the employee that originally developed the code, so we are looking for help. The Workbook is unlocked so we can get into the code; we just don't know how to fix.

Does anyone have tips on how to get this type of problem resolved? Are there freelance coders that specialize in things like this?

It seems like this might be a common problem. Maybe there are websites where people have posted how to resolve these compatibility type problems.

Any tips or points in the right direction would be appreciated.

Thanks in advance.

 

[SkipVought]

Hi,

Plz post your macro or the workbook.

Plz point out on what statement you get a Debug error.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Agent666]

Alternatively, if it's meant to achieve something relatively simple (like manipulation of data on a sheet vs complex calculations written entirely in VBA) then perhaps rewriting the code is fairly easily done. You didn't mention what the macros are ultimately trying to achieve.

 

[IDS]

Also is it 32 or 64 bit Office? I have had very few problems running old macros on 32 bit Office 2016, but 64 bit Office has more problems, especially if you have VBA connecting with external .dll files.

It is the Office bit level that is important, the Windows bits should make no difference.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[xwb]

Can you narrow down which part of the macro has stopped working?

 

[JG2828]

I think IDS is correct on this one. I am currently installing office 2016 and was reading through the notes about which version to install 32/64 bit. Under "reasons to choose 32 bit" was this:

Your VBA code uses Declare statements Most VBA code doesn’t need to change when using in 64-bit or 32-bit, unless you use Declare statements to call Windows API using 32-bit data types like long, for pointers and handles. In most cases, adding PtrSafe to the Declare and replacing long with LongPtr will make the Declare statement compatible with both 32- and 64-bit. However this might not be possible in rare cases where there is no 64-bit API to Declare. For more information about what VBA changes are needed to make it run on 64-bit Office, see 64-Bit Visual Basic for Applications Overview.
​

 

[R.Wheeler]

The original post was from my coworker as I didn't have an account on this site at the time. I have worked through some of the issue but one still remains. The process is suppose to take data from excel files and creates temp txt files then it is suppose to combine them all together into one word document. The following bit of code is where it is still getting hung up:

Sub Makedoc()

Application.ScreenUpdating = False
Set appWD = CreateObject("Word.Application")
appWD.Visible = True
appWD.Documents.Add
For x = 2 To 10
file = "C:\TEMP\temp" & x & ".prn"
appWD.Documents.Open Filename:=file
appWD.Selection.WholeStory
appWD.Selection.Copy
appWD.ActiveWindow.Close
appWD.Selection.Paste
Next x

It throws up the following error:
Run-time error '91':
Object variable or With block variable not set

and the debugger highlights the "appWD.Selection.Paste" line.

I have attached the spreadsheet as well.

Any help would be appreciated.

 

[dik]

Can you post a sample data file and I'll see if it works? The sheet opens fine using Open Office 4.1.3 on Win7, 64 bit. I've saved it and attached it... try uploading it and see if OO manages to correct something.

Should have added that my system is clean and there are no 'gremlins'.

Dik

 

[R.Wheeler]

The issue is not when trying to open the sheet. In the attached zip file you will see another spreadsheet called "SYS K 5 YR". In that spreadsheet when I click on the print system button is when the error occurs. Also in the zip file are all the files with the data in them as well as the temp files that were created as I don't know exactly what you are looking for.

 

[dik]

I can load the *.csv files as sheets and can open SYS K 5 YR spreadsheet and get the attached screen shots. None of the buttons on the SYS... file are active. I can click on them and they do nothing. Is that the problem? See if all 3 are attached...

Dik

 

[dik]

Nope... just the last one... first one attached...

 

[dik]

Second one...

 

[R.Wheeler]

That is not the issue as the buttons work for me. The process goes like this:
1. you output all your data into the .csv files
2. Open the "SYS K 5 YR" and the "Geopak System Format" spreadsheets and enable all macros
3. In the "SYS K 5 YR" you click load all data files and it populates all the tabs in that spreadsheet from the csv files
4. You click create temp files and it creates the .prn files
5. Final step, which is where it is bogging down, you click print system and it is suppose to combine all the .prn files into one word document and format that document

 

[cowski]

Older versions of Word and Excel would open multiple documents within the application and you could switch between documents for operations such as copy & paste. Newer versions open up each document in its own application instance. In other words, in older versions, if you opened 3 documents in Word, one instance of Word would show up in the task manager; by working with Word, you could switch between the 3 documents at will. Newer versions will show 3 instances of Word running, each with their own document; to switch between the documents, you must switch applications. My theory, which I have not had time to test, is that when you close the active window, that essentially shuts down that application instance and now the appWD variable goes out of scope and can no longer be referenced (e.g. by the paste operation). Again, this is just my untested theory; but, if correct, you will need to rewrite the subroutine to correctly handle how documents are managed within Word.

 

[dik]

R.Wheeler:
Can you tell me the file name and/or the location where the *.doc file is stored? I cannot locate it, if it is created. When I enter all three buttons, nothing appears to happen.

Dik

 

[R.Wheeler]

It doesn't automatically save it to your computer. The document will be open when the process is complete and you can save it wherever you like with whatever name you like. I think what "cowski" stated above is what the issue is and I have tried to modify that section of code but the best I can get it to do is get all the text into one document but it won't format the text because there are other blank word applications open.

 

[dik]

I deleted the *.prn files and the program does not recreate them... I'll see if I can access the macros this evening.

Dik

 

[cowski]

dik,
I don't think that Open Office will be able to run the Excel/Word macro as-is.

https://wiki.openoffice.org/wiki/Documentation/FAQ/Macros/Can_I_use_my_Microsoft_Office_macros?

www.nxjournaling.com